Sign in to follow this  
Followers 0

Wildcard search in a MySQL Number field

6 posts in this topic

Posted

For finding records starting with "213" in a field number with 6 digits in FM one would use "213###" and it would return ALL invoices starting with 213. Now that we have an ODBC connection to our MySQL db the wildcard search returns an error: "This field is defined to contain numeric values only. You must enter a numeric value".

My question is: Which wildcard character should I use to replace the "#" when reading MySQL db through an ODBC connection?

I unsuccessfully tried "%" and "_".

Share this post


Link to post
Share on other sites

Posted (edited)

I can't test it right now but this should work [color:blue]WHERE numberField like '123_';

Depending upon the length, you may need % on the end to represent unknown length. YOu haven't given your SQL statement but LIKE is what (I believe) should be used.

Edited by

Share this post


Link to post
Share on other sites

Posted

If I understand correctly I should include a SQL LIKE query in a script since the "Find" cannot be entered directly in the field searched?

Share this post


Link to post
Share on other sites

Posted (edited)

Sorry, no, I thought you were looking for the correct SQL query. I can confirm it won't work when searching in a SQL Server number field at least.

You may need to use a range, such as 123001...123999. I'm unsure why it wouldn't work but it may have something to do with FMs inability to index a SQL field for the search (create a value index) but this is just a guess. We can certainly search FM number fields using # as you've indicated.

Maybe others can offer more clarity.

Edited by

Share this post


Link to post
Share on other sites

Posted (edited)

I think searching using the range 213000...213999 will always be a ton more efficient anyway.

Even in FM, searching "213###" is *not* taking full advantage of the index on the table if the field is a number field, I believe FileMaker ends up scanning all records looking for ones matching the pattern - this makes sense when the pattern is more general (say, "##37#"), but since you can actually express your query as a numeric range you should definitely use that.

Edited by

Share this post


Link to post
Share on other sites

Posted (edited)

Thank you The Shadow and LaRetta for your help.

I have used successfully: ">123000<123500" entered directly in the field number and it returns all numbers between 123001 and 123499 (this is good for searching invoices for example) but for situations described by shadows such as "##30##" in a product code field I haven't yet find out how to address the problem.

To The Shadow: I am searching not a FM db but a MySQL db through an ODBC FM connection where the # wildcard does not work.

Your recommendation of using "123000...123500" directly entered in the number field works actually better than mine since it returns all invoices between 123000 and 123500. It neatly replaces the > or = to and < or = to I was looking for :

Edited by

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0