CODEWITHSUNDEEP
mysqlsqlstringwhere-clausesql-like
Claims
Claims

Reputation: 270

Sql pattern in like condition

Simple issue :

I have fields (path) like :

1/2/43
1/2/43/45
1/2/43/45/46

I want to be able to get the path containing 43/XX

Meanings here the only valid one would be

1/2/43/45

This seems not to be working

... WHERE path LIKE '%43/[0-9][0-9]%'

Upvotes: 0

Views: 48

Answers (1)

GMB
GMB

Reputation: 222382

Only SQL Server supports using (a small subset of) regular expressions with LIKE. In MySQL, you would use RLIKE, or REGEXP (both are synonyms). Your condition would translate as:

WHERE path RLIKE '43/[0-9][0-9]'

This can be shortened with a quantifier:

WHERE path RLIKE '43/[0-9]{2}'

You might want to be a little more specific by ensuring that the character that precedes 43 is either a slash or the beginning of the string:

WHERE path RLIKE '(^|/)43/[0-9]{2}'

The same constraint can be applied to the right side of the string:

WHERE path RLIKE '(^|/)43/[0-9]{2}(/|$)'

Upvotes: 2

Related Questions