where name like '1%' 1

[kasuals]

Ok guys.

I have a database that has this entry:

COL NAME=NAME VALUE="1 TestColumn"

if I do a search for '1%' I get nothing back. If I do a search in any of my other tables for that it prints fine.

Any ideas?

http://www.angsttechnology.com

- "Delightfully confusing..." raves The New York Times

-kas

 

[sleipnir214]

More specifics, please.

Exactly what does your query look like?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[kasuals]

Well, it must have been something I did... it works now...

However I am having a different problem.

This is the query I am having problems with now:

select * from gamemember where name like '0%' or name like '1%' or name like '2%' or name like '3%' or name like '4%' or name like '5%' or name like '6%' or name like '7%' or name like '8%' or name like '9%' and game='eab' order by name asc

It doesn't just select the names under game (eab in this case), it returns all names like that. Can I not use and in that way? Or do I have to do like and or like and or like and?

http://www.angsttechnology.com

- "Delightfully confusing..." raves The New York Times

-kas

 

[sleipnir214]

It may be an operator precedence issue.

You're interpreting your where clause as:
(name like '0%' or name like '1%' or ...... or name like '9%') and (game = 'eab')

But MySQL is interpreting it as:
(name like '0%' or name like '1%' or ...... or name like '8%') or (name like '%9' and game = 'eab')

You might try explicitly using parentheses.

But you also have available searching strings by regular expressions. Using a regular expression would reduce your query to:

SELECT * FROM gamemember WHERE name regexp '^[[:digit:]]' and game = 'eab' order by name

And also eliminates the possible operator ambiguity. The function "name regexp '^[[:digit:]]'" can be interpreted as "name begins with ('^') a digit ('[[:digit:]]')"

More on regular expressions here:

http://www.mysql.com/doc/en/Regexp.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[sleipnir214]

ADDENDUM:
It is an operator precedence problem. With a much simpler table, I can verify that the query:

select * from foo where name like '0%' or name like '1%' and game = 'a'

produces a problem similar to yours. Rewriting the query as:

select * from foo where (name like '0%' or name like '1%') and game = 'a'

produces output like you are expecting.

I still recommend using the regular expressions, though.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[kasuals]

News to me. I didn't know you could use regular expressions. You get yourself a big fat star... you just opened up quite a few doors for me. Shows how much I know huh?

http://www.angsttechnology.com

- "Delightfully confusing..." raves The New York Times

-kas