1

Consider this SQL table

id | name | numbers
------------------------
1 | bob | 1 3 5
2 | joe | 7 2 15

This query returns the whole table as its result:

SELECT * FROM table WHERE numbers LIKE '%5%'

Is there an SQL operator so that it only returns row 1 (only columns with the number 5)?

IMB
  • 15,163
  • 19
  • 82
  • 140
  • 7
    Do not store numbers in a delimited string. It is a bogosity. Use a separate table with one row per "name" and "number". – Gordon Linoff Oct 02 '17 at 19:11
  • SELECT * FROM table WHERE numbers LIKE '% 5 %' – Leonardo Cabré Oct 02 '17 at 19:12
  • Possible duplicate of [Check if column containing json string has specific value](https://stackoverflow.com/questions/46514613/check-if-column-containing-json-string-has-specific-value) – Ravi Oct 02 '17 at 19:17
  • If you need to do it once you can use `FIND_IN_SET` with `REPLACE`, but if you need to use it every time - follow @GordonLinoff advice. – cn007b Oct 02 '17 at 19:29
  • @GordonLinoff I understand it's bogosity and your suggestion is better. If I use a `-`as string delimiter e.g., `-1-3-5-`, `LIKE '%-5-%'` works. It's still a mess but I just want to know if there's any major issues on this bogosity approach? I generally want to stick to 1 table as this is a simple app. – IMB Oct 02 '17 at 19:45

2 Answers2

3

Use regexp with word boundaries. (But you should ideally follow Gordon's comment)

where numbers REGEXP '[[:<:]]5[[:>:]]'
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
2

It's a pity that you are not using the comma as a separator in your numbers column, because it would be possible to use the FIND_IN_SET function, but you can use it together with REPLACE, like this:

SELECT * FROM table WHERE FIND_IN_SET(5, REPLACE(numbers, ' ', ','));
JonathanDavidArndt
  • 2,518
  • 13
  • 37
  • 49
cn007b
  • 16,596
  • 7
  • 59
  • 74