MYSQL - Match/Find a number in a string

Question

I have a need to find a particular number from a column, say I look for number 9 in table tours which has a column city. The column city has a string of numbers like

9
49
5,9
4,94
5,8,89,32

and I need to find the number 9 in these so the only results I would want would be

9
5,9

I have tried using REGEX but can't get it right. Can anyone point me to the right direction? Here's the query so far

SELECT 
    a.title, a.tourprice, a.city, b.city AS destCity
FROM
    tours a
        RIGHT JOIN
    (SELECT 
        id, city
    FROM
        destinations
    WHERE
        id = 47) b ON a.city LIKE CONCAT('%,', b.city) OR a.city LIKE CONCAT(b.city, ',%') //b.city evaluates to 9

I realize there may or may not always be a comma before or after the number I want.

Answer 1

Try this;)

SELECT
    a.title,
    a.tourprice,
    a.city,
    b.city AS destCity
FROM tours a
RIGHT JOIN (
    SELECT id, city
    FROM `destinations`
    WHERE id = 47
) b ON FIND_IN_SET('9', a.city) > 0

And if a.city is not all separated by comma ,, I think REGEXP will be more advisable, like the answer @Wiktor Stribiżew has given.

Answer 2

I think you missed 1 possible scenario with LIKE: if a 9 appears in the middle of the entry. So, add

OR a.city LIKE CONCAT('%,', b.city, ',%')

If you want to have a go with a regex, try

WHERE a.city REGEXP CONCAT('[[:<:]]', b.city, '[[:>:]]')

where the [[:<:]] is a leading and [[:>:]] is a trailing word boundaries. However, in such a simple situation, you can do without a REGEXP and use LIKE due to the fact that REGEXP in MySQL is rather resource-consuming.

Answer 3

There's a MySQL built-in function, FIND_IN_SET which does what you want.

It returns the 1-based index of the first argument in the second argument, where the second argument is a comma-separated string. If the first argument isn't present in the second at all, it returns 0.

SELECT FIND_IN_SET(9, '9');           -- 1
SELECT FIND_IN_SET(9, '49');          -- 0
SELECT FIND_IN_SET(9, '5,9');         -- 2
SELECT FIND_IN_SET(9, '4,94');        -- 0
SELECT FIND_IN_SET(9, '5,8,89,32');   -- 0
SELECT FIND_IN_SET(9, '5,8,89,9,32'); -- 4

Since MySQL treats 0 as FALSE and any non-0 number as TRUE, you can use it in a WHERE clause or in your JOIN.

RIGHT JOIN ... ON FIND_IN_SET(b.city, a.city)

But there are several reasons why storing a comma-separated list like this is a bad idea. You should prefer a separate table with a row for each combination of tour and city.