mysql two/three column treat as one and then LIKE search and order

Question

Is it possible to treat two or three column treat (merge) as one column and search with LIKE keyword from that one column and ORDER result as desired?

my columns

| to_place   | from_place  | departure_place|
|:-----------|------------:|:------------: |
| Same       |    Place    |    Line     
| Rangpur    |    Dhaka    |    Chittagong   
| Badda      |    bogra    |    hello     
| Shyamoli   |    rajshahi |    mohammadpur      
| test       |    test1    |    adabor    
| another    |    test2    |    kurigram

so i want these 3 columns in one column in my SQL so that i can order them

Partial duplicate of http://stackoverflow.com/questions/2514548/how-to-search-multiple-columns-in-mysql. — Nathan Tuggy, Dec 31 '15 at 04:15
you may user inner queries for this. Inner query will concatenate the columns (as many you want to concatenate) and outer query will apply a like constraint. — Vinay Prajapati, Dec 31 '15 at 04:18
What's the separator you would use for concatenation or just no separator? — Vinay Prajapati, Dec 31 '15 at 04:27

score 5 · Answer 1 · answered Dec 31 '15 at 04:41

5

I've tried this and it is working.

SELECT CONCAT(`to_place`, `from_place`,`departure_place`) AS derived_place
FROM `yourtable`
HAVING derived_place LIKE '%search_value%'
ORDER BY derived_place;

I hope this helps.

answered Dec 31 '15 at 04:41

Jis Jose

614
1
6
15

@RejoanulAlam Then why is this not the accepted answer? – Nivin V Joseph Dec 31 '15 at 06:11
@Jis Jose I have accepted an answer which more closure to my requirements. but your effort help me. – Rejoanul Alam Dec 31 '15 at 06:36
however my one of the requirement was "get top ordering column name" – Rejoanul Alam Dec 31 '15 at 06:38

Niagaradad · Accepted Answer · 2015-12-31T04:31:06.963

4

SELECT * FROM (
  SELECT to_place as Location FROM yourtable
  UNION ALL
  SELECT from_place FROM yourtable
  UNION ALL
  SELECT departure_place FROM yourtable) as DERIVEDTABLENAME
ORDER BY 1

actually you can merely use UNION as UNION ALL would return the same city name if it was in more than one column. that wouldn't really be necessary.

edited Dec 31 '15 at 04:31

answered Dec 31 '15 at 04:16

Niagaradad

453
3
10

not working. showing `#1248 - Every derived table must have its own alias` error and there are no `LIKE` keyword – Rejoanul Alam Dec 31 '15 at 04:23
why do you need such union all kinda thing? Can't we just concatenate? – Vinay Prajapati Dec 31 '15 at 04:27
2

I added the derivedtablename, it can be anything, sorry left it out. Beyond that are you telling me you don't know how to add a WHERE clause to to do the LIKE search? – Niagaradad Dec 31 '15 at 04:27

mysql two/three column treat as one and then LIKE search and order

2 Answers2