regular expression search a number from string

this question maybe very simple for you. i'm using mysql regexp statment开发者_运维技巧.

myQuery is

select * from contents where categories regexp '{myPattern}';

categories field area 54,25,99,4,2... etc string.

my question how can i find only number of '4' from categories field.

sorry for my english. help me please.

… WHERE FIND_IN_SET('4', categories) > 0

better yet to normalize your db scheme with categories in their own table and then join these tables together m:n

The way to match "any cvs string containing '4' as a value in the string" is:

mycolumn regexp '[[:<:]]4[[:>:]]' 

In mysql regex, [[:<:]] means "start of word" and [[:>:]] means "end of word".

You can use this:

where categories RLIKE '(^|,)4($|,)';

This will match when categories contains 4, followed by a , or nothing, and precedeed by , or nothing.