Help correctly to make a selection of the database.
I have a VARCAR field, it stores the following value: '5.26,7'.

How to select all records where is '7'?

I tried LIKE' om, but it turns out a bit wrong. Suppose you need '7', then I write LIKE '%7%' , but mysql will return all records where this number is present, including '17', '27'. I have a maximum of two-digit numbers.

    2 answers 2

    Offhand, without checking - SELECT ... WHERE column REGEXP '(^|,)[[:space:]]*7[[:space:]]*(,|$)'

    True, such a query looks wild and if it will be executed more than once in a lifetime, then it is probably worth considering whether it is necessary to normalize the base.

    Another search for numbers in the list is found on SO - ... WHERE find_in_set('7', column) > 0 .

    MySQL is not at hand, unfortunately, there is nothing to check.

    Another option is mentioned on SO ... WHERE 7 IN (column) , but it is incorrect . As I understand it, so IN cannot be used , because:

     column IN ('1,2,3') ≡ column IN (CAST('1,2,3' AS INT)) ≡ column IN (1)

    Those. The results will be partially plausible, but erroneous.

    • Thank you very much. I checked the first two options - it works. Applied option 2, because The shortest. - Idaho37
    • @ Idaho37: Found it, write that the variant with IN against find_in_set is incorrect. Sorry for the misinformation. Use find_in_set . - drdaeman pm
    • I realized that IN with a string does not work for example IN ('2,52,4'), but since IN (2,52,4) everything is looking for. And I have a string so I noticed that I am looking for if the desired number is at the beginning of the line, for example, 2 IN ('2,52,4'). And the first option works well. I'll try the third one. - Idaho37
    • Yes, find_in_set is looking for the right one too. Thank. - Idaho37
    • You can greatly simplify the regular expression. I will bring it into PCRE, remake it in a POSIX engineering case. \ b7 \ b - ReinRaus pm
     SELECT '7,26,17' REGEXP '^7';
    • Not the fact that the required number will be at the beginning. - Idaho37