[RESOLVED] Select from database where first 3 characters equal 'BMX'

middleman666

Hi people,

just a quick one (hopefully):

I want to select entries from a database where the first 3 characters of one of the fields (title) are equal to 'BMX'.

Is there a way of doing something similar to this as a query?:

substr($row['title'],0,3);

Thanks.

etully

select name,address,city,state from customers where bike='BMX%";

EDIT: Ignore this entry. Correct syntax below.

middleman666

cheers.

etully

cheers for the reply.

Trouble with that is, some of my titles have 'this is a bmx' - I want it only to select those with 'bmx' as the first 3 characters.

Thanks.

Give it a try. I think you'll be surprised.

etully

oops. I made a mistake. I meant to write:

select name,address,city,state from customers where bike like 'BMX%';

/me places foot in mouth

middleman666

Hi again,

I tried what you suggested, but I seem to be getting unexpected results.

For example, I am trying to get the entries without BMX in the title using:

"SELECT * FROM $TableName where  title not like 'BMX%'"

But this seems to miss out some entries where the title doesn't have bmx in the title at all.

Am I doing something wrong?

thanks.

etully

To be honest, I've never tried it with "not". Can you give me an example of one of the titles that is getting missed?

etully

This simple test seems to work for me but I might be missing something obvious.

mysql> select * from cheers;
+--------+
| name   |
+--------+
| eric   |
| bob    |
| ericka |
| eratic |
+--------+
4 rows in set (0.00 sec)

mysql> select * from cheers where name not like 'eri%';
+--------+
| name   |
+--------+
| bob    |
| eratic |
+--------+
2 rows in set (0.00 sec)

cgraz

NOT LIKE 'BMX%' means to return all rows where BMX is not in the beginning of your title. Is this always the case, or can BMX be elsewhere in your title? Is BMX always capitalized?

If you're looking for all titles that don't have BMX anywhere in the string, then you need

WHERE title NOT LIKE '%BMX%'

etully

EDIT: Not sure what the OP wants

middleman666

Sorry guys, it was my bad. I had two queries running and I only changed one of them so I thought it wasn't working properly... turns out it works fine 🙂

Thanks for all your help... I'll go bury my head in shame... :S