Wild Card String Searching In SQL

[baseballgem]

I am using VFP 7, and I am exploring ways of employing Fuzzy Matches in String Searches.
I know it's possible in MS SQL. But in VFP7 how would one query with a wildcard. I know about the LIKE "U_" examples they show and the LIKE "C%" example.

(lets use the ? -question mark as a wild card)
How would you do this: I want to see everyone whose last name is ?LMAN. Which I want to yield OLMAN, ULMAN, ALMAN, ELMANDORF ...

Would I need an index on SUBS(lname,2) then just query for that in the SQL. ...where SUBS(lname,2) = "LMAN". But this is just too hard wired for me.

So what if it's a variable such as lname = "B?L": Then I might try "BA?" The wild card position may vary; or if am looking for Carter Street and not sure of spelling. ...where Address LIKE "C?RT?R"

So it might return CURTAR, CIRTIR, CARTAR, and CARTER

Are such queries possible in VFP7, AND can they be performed on Free Tables.

 

[Mike Gagnon]

baseballgem

How would you do this: I want to see everyone whose last name is ?LMAN. Which I want to yield OLMAN, ULMAN, ALMAN, ELMANDORF ...

CREATE CURSOR myCursor (name)
INSERT INTO myCursor (name) values ("OLMAN&quot
INSERT INTO myCursor (name) values ("ULMAN&quot
INSERT INTO myCursor (name) values ("ALMAN&quot
INSERT INTO myCursor (name) values ("ELMANDORF&quot

SELECT name from myCursor WHERE "LMAN" $ NAME

You can also use the wildcard like this:
SELECT name from myCursor WHERE LIKE ("*M*N*",MYCURSOR.name) Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ramani]

Hi

what you need is the $ operator.

If you give command..
SET FILTER TO "LMAN" $ FieldLastName

what you have to be careful is that..
cSearch $ cSearchedOnField
This cannot be coded the otherway. i.e. left hand side of $ operator is what you are searching for.. and the right hand side of $ operator is your Last name field.

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[Mike Gagnon]

baseballgem

CREATE CURSOR myCursor (name)

this should read
CREATE CURSOR myCursor (name c(20))
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[rgbean]

Actually,
I might use the more generic SQL "like" way of doing it:

SELECT name from myCursor WHERE NAME LIKE "%LMAN%"
* OR
SELECT name from myCursor WHERE NAME LIKE "_LMAN%"

Also, for "...Carter Street and not sure of spelling. ...where Address LIKE "C?RT?R"

SELECT name from myCursor WHERE NAME LIKE "C_RT_R"

Rick

 

[KoenPiller]

Hi Baselbalgem,

I had following procedure to locate in a dictionary with wildcards, the common * and ? wildcards have to be replaced with the SQL _ and %. With the following procedure one is able to produce an SQL array, cursor or whatever with any possible combinations.


LOCAL lcNwWord, lcWord, lcN, lnI

lcNwWord=""
WITH ThisForm
lcWord=ALLTRIM(LOWE(.cWord.Value)) &&substitute .cWord with your textbox name
DIME myArray(1)
FOR lnI=1 TO LEN(lcWord)
lcN = SUBS(lcWord,lnI,1)
DO CASE
CASE lcN="*"
lcN="%"
CASE lcN="?"
lcN="_"
ENDCASE
lcNwWord=lcNwWord+lcN
ENDFOR
ENDWITH

SELECT TOP 35000 word FROM Mydictionary ;
WHERE compwoord LIKE lcNwWord AND DELETED()=.F. ;
ORDER BY Mydictonary.compword INTO ARRAY.MyArray

Hope this also replies to your question.

Regards,

Koen