Jump to content

  •  

Photo

Wildcard search in a MySQL Number field


  • Please log in to reply
5 replies to this topic

#1 plh212  Enthusiast

plh212
  • Members
  • 50 posts
  • FM Application:11
  • Platform:Mac OS X Snow Leopard
  • Skill Level:Intermediate
  • Time Online: 2h 58m 44s

Posted 27 December 2008 - 10:44 AM

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 "_".
  • 0
Philippe
FM Pro 11 - Mac OS X
MySQL

#2 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 24m 53s

Posted 27 December 2008 - 11:10 AM

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

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.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#3 plh212  Enthusiast

plh212
  • Members
  • 50 posts
  • FM Application:11
  • Platform:Mac OS X Snow Leopard
  • Skill Level:Intermediate
  • Time Online: 2h 58m 44s

Posted 27 December 2008 - 12:58 PM

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?
  • 0
Philippe
FM Pro 11 - Mac OS X
MySQL

#4 LaRetta   Lifelong FM Student

LaRetta
  • Members
  • 9,680 posts
  • LocationOregon
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Time Online: 206d 17h 24m 53s

Posted 27 December 2008 - 06:21 PM

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.
  • 0
Each assumption is an educated guess, a likely condition or event, presumed known and true in the absence of absolute certainty.

#5 The Shadow  veteran

The Shadow
  • Members
  • 773 posts
  • FM Application:10 Advance
  • Time Online: 36m 10s

Posted 27 December 2008 - 06:56 PM

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.
  • 0
The Shadow Knows...

#6 plh212  Enthusiast

plh212
  • Members
  • 50 posts
  • FM Application:11
  • Platform:Mac OS X Snow Leopard
  • Skill Level:Intermediate
  • Time Online: 2h 58m 44s

Posted 28 December 2008 - 06:53 AM

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 :
  • 0
Philippe
FM Pro 11 - Mac OS X
MySQL




FMForum Advertisers