Wildcard search in a MySQL Number field

[plh212]

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 "_".

[LaRetta]

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 December 27, 2008 by Guest

[plh212]

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?

[LaRetta]

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 December 28, 2008 by Guest

[The Shadow]

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 December 28, 2008 by Guest

[plh212]

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 December 28, 2008 by Guest