PHP SQL - SQL regexes?

Author

SQL regexes?

Matthew Del Buono

2004-06-28, 3:56 am

I've been thinking about ways to search a database in PHP. I remember once I
asked someone how to find something contained within a message, he said to
do WHERE <var> LIKE "%blah%"

So now I think.... %, that's like a regex. Is that what LIKE is for? It
allows you to do regexes?

Thanks for any clarification!
-- Matt

Ben Gribaudo

2004-06-28, 4:08 pm

Hi Matt,

LIKE is not a regex. It allows you to do use wildcards when querying
string-containing fields. See:
http://dev.mysql.com/doc/mysql/en/S..._functions.html

MySQL supports regular expressions. See:
http://dev.mysql.com/doc/mysql/en/Regexp.html

Hope this helps!

Ben :-)
Ben Gribaudo - Baltimore, MD - www.bengribaudo.com

"For God so loved the world, that he gave his only begotten Son, that
whosoever believeth in him should not perish, but have everlasting life."
John 3:16

"Matthew Del Buono" <MattDelB@nospam.com> wrote in message
news:m9NDc.1914$fd3.125@lakeread04...
> I've been thinking about ways to search a database in PHP. I remember once
I
> asked someone how to find something contained within a message, he said to
> do WHERE <var> LIKE "%blah%"
>
> So now I think.... %, that's like a regex. Is that what LIKE is for? It
> allows you to do regexes?
[snip]

Andy Hassall

2004-06-28, 4:08 pm

On Mon, 28 Jun 2004 00:30:10 -0400, "Matthew Del Buono" <MattDelB@nospam.com>
wrote:

>I've been thinking about ways to search a database in PHP. I remember once I
>asked someone how to find something contained within a message, he said to
>do WHERE <var> LIKE "%blah%"
>
>So now I think.... %, that's like a regex. Is that what LIKE is for? It
>allows you to do regexes?

No, just simple wildcard matches.

% matches zero or more of any character.
_ matches one of any character. (May be zero or one - can't remember off the
top of my head).

LIKE is part of the SQL standard. Some databases have extensions that allow
full regular expression matching.

Some LIKE queries can still use indexes, i.e. when it's a trailing wildcard,
it can use the constant prefix to scan a range across an index. If it's a
leading wildcard, it won't be able to use an index.

Regular expression matches are even less likely to be able to use an index,
unless the database puts a fair bit of effort into interpreting the regex.

--
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

Sponsored Links