LIKE syntax problem

[Jonathan]

Hello,

I try to develop a query to match wildcards following the LIKE
guidline in the MySQL manual
(http://dev.mysql.com/doc/mysql/en/pattern-matching.html)

Who can help me solve the matching problem? The string I am looking
for consists of 2 charachters a capital B folowed by one digit not
neccesarilly at the beginning of the record.
However it does not return results when it should in my opinion do so.
See the following sample:

SELECT firstname
FROM `patient`
WHERE firstname LIKE '%B[0-9]%' LIMIT 10

returns:

nothing

SELECT firstname
FROM `patient`
WHERE firstname LIKE '%B_[0-9]%' LIMIT 10

returns:

nothing

SELECT firstname
FROM `patient`
WHERE firstname LIKE '%B[0-9]_%' LIMIT 10

returns:

nothing

whereas the following statement

SELECT firstname
FROM `patient`
WHERE firstname LIKE '%B%' LIMIT 10

returns:

+-------------------+
| firstname |
+-------------------+
| B1O1V2 LM |
| B102v3ap |
| B. |
| B. |
| HUBERTINA |
| Tobben |
| B1O1V2 LI LAT LOK |
| B1o1v2 Lm |
| Boost |
| B1o1v1 Ml |
+-------------------+

What am I doing wrong? Or is this only supported by MySQL version 4.1
and up or something like that? I'm running MySQL version 4.0.22-nt-max
on a MS Windows 2000 server environment.

Thanks!

Jonathan

[Steve]

No, this is not MySQL 4+ only, but your syntax is wrong. Compare with:

http://dev.mysql.com/doc/mysql/en/regexp.html

e.g.

SELECT firstname
FROM `patient`
WHERE firstname REGEXP '%B[0-9]_%' = 1
LIMIT 10

---
Steve

[Jonathan]

On 24 Jan 2005 09:32:35 -0800, "Steve" <go********@nastysoft.com>
wrote:

No, this is not MySQL 4+ only, but your syntax is wrong. Compare with:

http://dev.mysql.com/doc/mysql/en/regexp.html

e.g.

SELECT firstname
FROM `patient`
WHERE firstname REGEXP '%B[0-9]_%' = 1
LIMIT 10

---
Steve

Thanks for the hint. It should be this way I believe:

SELECT firstname
FROM `patient`
WHERE firstname REGEXP '[b][0-9][V][0-9]' = 1

The % and _ placeholders/wildcards resulted in error messages.

Jonathan

[Steve]

X-No-Archive: yes

Oops, sorry, should have looked more closely. Glad you have it working
now.

---
Steve

[Bill Karwin]

Jonathan wrote:

WHERE firstname REGEXP '%B[0-9]_%' = 1

Regular expressions and SQL LIKE expressions each come from different
heritage.

Character ranges such as [0-9] are not treated as metacharacters in LIKE
expressions in SQL. The only wildcards in LIKE expressions are % and _.
Conversely, the % and _ characters have no special meaning in regular
expressions.
WHERE firstname REGEXP '[b][0-9][V][0-9]' = 1

For what it's worth, this could be written equivalently as:
WHERE firstname REGEXP 'B[0-9]V[0-9]'
A character range of [b] is the same as simply a literal B.

Regards,
Bill K.