mysql RLIKE search for number in string

[ernstl]

How can i find only the integer '1' in a string like
1+11|111-1111
Something like
SELECT ... WHERE RLIKE '..1..'
?

[NogDog]

I'm not at all sure what you mean. I assume it's not something as simple as:

. . . WHERE column LIKE '%1%' . . .
? :confused:

[ernstl]

No:
There is a field in my table that contains strings of a pattern like maybe
1+11|111-1111 (Or 3+3+2|34-35-36 or sth. else)
I want to select those records that contain for example only the number ' 1 ' (OR the number ' 3 ' in the 2nd example) and not those which also contain the numbers ' 11 ', ' 111 ' or ' 1111 ' . The numbers in my strings are separated by the characters ' + ' , ' - ' and ' | ' .
For that purpose i need a regular expression that finds the number 1 ( or 11 or 111 etc ) and not only the character 1. This shall work with mysql on php.
I hope i explained it better now.

[NogDog]

I think maybe this is what you're after?

. . . WHERE column_name REGEXP '[[:<:]]1[[:>:]]'

(The [[:<:]] and [[:>:]] are "word boundaries" in the MySQL regexp syntax.)

[ernstl]

That does exactly what I want! Though I don't quite understand how it works. Do you have the patience to shortly explain the expressions [[:<:]] and [[:>:]] ?

Many Thanks!

[NogDog]

They are like the "\b" word boundary assertion in PHP's preg_* functions' regular expression syntax. Essentially they match on anything that is not considered to be part of a "word", which is letters, numbers, and/or underscores. So '[[:<:]]1[[:>:]]' matches if anywhere in the field there is a "1" that is both preceded and followed by either a non-word character or the start/end of the string.

[ernstl]

Many thanks! Now i have worked out my php-Query, which needed some escaping:

$query=mysql_query('SELECT column_name FROM table WHERE column_name RLIKE \'[[:<:]]176[[:>:]]\';');

That does it!

[psicloone]

How can I use RLIKE to search for '@'?

"SELECT * FROM messages WHERE message RLIKE '[[:<:]]@[[:>:]]' won`t work.