CODEWITHSUNDEEP
mysql
johnnal
johnnal

Reputation: 47

SQL wildcard between two strings

Suppose you have two tables:

            table1                         table2
       id | cityA | cityB              id | cities_queue|
        1     a        c                1    a , b , d
        2     s        f                2    a , b , c ,e
        3     d        m                3    a , m , d , e

I want to return only those rows of table 2 that includes cityA and cityB with this specific order, first cityA and after ( somewhere...) comes cityB. Because of that the only accepted result must be (a , b , c, e). First thought is to use LIKE command to find which table2 rows include cityA, then using substr() to receive only part of cities_queue that comes after cityA and using LIKE() again for cityB. So my question is: is it possible to use only once LIKE() holding as a string something like 

 (cityA) - % - (cityB)

where % = wildcard to find all cities_queue that include cityA and cityB regardless of what is between them; If so, please provide the right syntax. Thanks

Upvotes: 0

Views: 64

Answers (2)

Alex
Alex

Reputation: 17289

Not sure what is your point is but if order of elements has really important:

http://sqlfiddle.com/#!9/83459/7

SELECT t2.*
FROM table2 t2
INNER JOIN table1 t1
ON t2.cities_queue LIKE CONCAT('%',t1.cityA,' , ',t1.cityB,'%')

or

SELECT t2.*
FROM table2 t2
INNER JOIN table1 t1
ON t2.cities_queue LIKE CONCAT('%',t1.cityA,' % ',t1.cityB,'%')

Upvotes: 1

murison
murison

Reputation: 3983

Similar to @Alex, but I'd go with a regex:

SELECT *
FROM table2 t2
INNER JOIN table1 t1
ON t2.cities_queue regexp CONCAT(t1.cityA,'.*',t1.cityB)

http://sqlfiddle.com/#!9/83459/3

Upvotes: 0

Related Questions