Select query to get particular pattern

Question

I have a column which stores the data from the below format. ["12973","111","5555"].

I want to select the items which is not in the above set. for ex,

If I search 12973 if it is exactly match it will not return. When I use Like if I search "129" it also return the same result.

Any ideas to solve this problem..

So you want any row that does not contain 12973 in the first case and any row that does not contain a number starting with 129 in the second case? Also, what version of MySql is this for? — Joakim Danielson
– Joakim Danielson, Commented Jun 17, 2019 at 6:37

Joakim Danielson · Accepted Answer · 2019-06-17 06:40:51Z

1

If you are searching for an exact number you can use NOT LIKE to exclude the row

SELECT * FROM test WHERE col1 NOT LIKE '%"12973"%'

if you are searching for a row where no numbers start with 129 you can use REGEXP

SELECT * FROM test WHERE NOT col1 REGEXP '.*"129[0-9]*"'

answered Jun 17, 2019 at 6:40

Joakim Danielson

53.1k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcus · Accepted Answer · 2019-06-17 06:41:09Z

0

What about search all results with LIKE and remove the exact matches?

SELECT * FROM t WHERE a LIKE '%129%' AND a != '129'

answered Jun 17, 2019 at 6:41

Marcus

1,9402 gold badges18 silver badges28 bronze badges