regexp in sql statement

Here's my query so far

SELECT id, name, 'aSaraha' REGEXP "/\b" + name + "/i" FROM bs_bots

name is a table column.
The string 'aSaraha' will be text from a php script.

Ultimately the select column will become part of the where clause

Currently it always returns 0.

If I was to get all the names from the database I would be able to loop through the results and do:

if ( stristr( $userdata , $results["name"] ) )

but I'd like to get the database to do the work if possible.

Any ideas?

I don't think MySQL's REGEXP supports the "\b" - word boundary.

You have to use the following instead -

[[:<:]]word[[:>:]]

Try -

SELECT id, name, name REGEXP "[[:<:]]aSaraha[[:>:]]" FROM `bs_bots`

thanks, I'll try that!

this is what I've got now

SELECT `id`, `name`, 'xSaraha' REGEXP concat("[[:<:]]" , `name`, "[[:>:]]") FROM `mytable`

If it's an exact match I get 1 otherwise 0

the documentation says it's

expr REGEXP pat

so, the datebase name column holds the pattern I need to match - ie Sarah - and the string from my form holds the more complext string ie "expr".

so, looking at the info for [[:>:]] I worked out it wasn't quite what I wanted as I wasn't searching for distinct words

tried this

SELECT id, name, "xSarahz" REGEXP concat("^[an]",`name`, "[an]$") FROM `mytable`

but that didn't work either.

Any more ideas anyone?

I need to performance test this but here's what worked in the end

SELECT id, name FROM `mytable` where "xSarahz" like concat("%",`name`, "%")