Using Where and Like in a query

[kiwieur]

Hi,
I am new to MySQL and I am trying to write a query using "WHERE" and "LIKE" but seem to be having problems with the syntax.

In MS Access I do not have a problem and this is the SQL for an Access Query

SELECT SITEMP.machine_number, SITEMP.instruction
FROM SITEMP
WHERE (((SITEMP.instruction) Like "####*"));

And this works just fine, Could someone point me in the right direction using MySQL please

Regards

Paul

 

[r937]

mysql uses the percent sign instead of the asterisk for the wildcard character

r937.com | rudy.ca

 

[kiwieur]

Ok, I have changed the * to % but still no joy this is my query in MySQL

SELECT S.job_number, S.machine_number, S.instruction FROM SITEMP S
WHERE (((S.instruction) Like "####%"));

Regards

Paul

 

[r937]

i'm sorry, i'm not familiar with the "no joy" error message -- were there any other clues as to what happened?

r937.com | rudy.ca

 

[kiwieur]

Sorry about that, when I run the query i have no rows of data returned. There are no error messages just no records, however I know there is data because i have run a query from MS Access and 4 rows were returned

Regards

Paul

 

[r937]

could you please display the contents of the instruction column from the 4 rows that you know to exist

r937.com | rudy.ca

 

[kiwieur]

r937,

here is the contents as requested, these are from my MS access query which is linked to a table in MySQL

instruction
1201
1502
2302
2404 Paul Test 2
2404 Paul Testing

as you will see the first 4 digits are numeric after that i do not care wether they are alpha or numeric

I appreciate you looking at this for me

Regards

Paul

 

[r937]

you know, i completely forgot that the hash marks are wildcards for digits in access

okay, in mysql you want this:

... where S.instruction regexp '^[[:digit:]]{4}'

r937.com | rudy.ca

 

[kiwieur]

r937,

Thank You, that works perfectly

Thanks for your help

Paul