CODEWITHSUNDEEP
mysqlsql-like
Adam
Adam

Reputation: 1975

MySQL LIKE matching whole numbers only

I am trying to get the records from my database that have a number in a vertical pipe separated value.

I have my query like this:

SELECT * FROM level_3 

WHERE 
    date_end = -6 

AND 
    ccode = 'GB'

AND ( 
    responsible LIKE '%5%' OR 
    accountable LIKE '%5%' OR 
    consulted LIKE '%5%' OR 
    informed LIKE '%5%'
    )

And here are the results:

date_end    ccode    responsible    accountable    consulted    informed
-6          GB       |6|            |6|            |8|9|15|     |8|
-6          GB       |6|5|          |6|            |8|          |16|

However it is matching 15 for LIKE %5% but I only want it to match the exact number. How can I go about doing this?

Upvotes: 0

Views: 4449

Answers (3)

nl-x
nl-x

Reputation: 11832

Whay you would like is something like

Where concat('|',responsible,'|') like '%|5|%'

Upvotes: 0

Joop Eggen
Joop Eggen

Reputation: 109557

There is a regular expression pendant to LIKE.

SELECT * FROM level_3 
WHERE date_end = -6 
AND ccode = 'GB'
AND ( 
    responsible REGEXP '\|[0-9]+\|' OR 
    ...
)

(Remark for other uses: use '^ ... $' for total matching from begin ^ to end $.)

Upvotes: 2

Jeremy
Jeremy

Reputation: 1521

Try this query:

SELECT * FROM level_3 

WHERE 
    date_end = -6 

AND 
    ccode = 'GB'

AND ( 
    responsible LIKE '%|5|%' OR 
    accountable LIKE '%|5|%' OR 
    consulted LIKE '%|5|%' OR 
    informed LIKE '%|5|%'
    )

Upvotes: 3

Related Questions