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]

Re: LIKE syntax problem


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]

Re: LIKE syntax problem

On 24 Jan 2005 09:32:35 -0800, "Steve" <googlespam@nas tysoft.com>
wrote:
[color=blue]
>
>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[/color]

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]

Re: LIKE syntax problem

X-No-Archive: yes

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

---
Steve

[Bill Karwin]

Re: LIKE syntax problem

Jonathan wrote:[color=blue][color=green]
>>WHERE firstname REGEXP '%B[0-9]_%' = 1[/color][/color]

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.
[color=blue]
> WHERE firstname REGEXP '[B][0-9][V][0-9]' = 1[/color]

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.