March 28, 2024, 06:23:17 PM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


String search in MDB

Started by Brian, October 16, 2012, 12:28:34 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Brian

Hi,

Say I have a field with a string like "Roger Rabbit." If I enter an SQL search and just put in "rabbit,"
how can I check to see if my search term - rabbit - has any characters before or after the original
field string?

This has to do with SQL searches, in that you could put "%rabbit%" and it will find records that has
the word rabbit in it with characters either side of the search term

Or you could put "rabbit%" and it will find fields that start with rabbit, or "%rabbit" that finds fields
that end with the word rabbit

So if I knew there were characters "before" or "after" the search term, I could adjust the wildcards as
needed, and write the appropriate code before executing

Sorry if this is a ramble...

Brian

billhsln

October 16, 2012, 12:44:04 PM #1 Last Edit: October 16, 2012, 12:49:09 PM by billhsln
SELECT '1'
FROM TABLE
WHERE FIELD LIKE '%RABBIT'
UNION
SELECT '2'
FROM TABLE
WHERE FIELD LIKE 'RABBIT%'
UNION
SELECT '3'
FROM TABLE
WHERE FIELD LIKE '%RABBIT%'

1 says characters are in front, 2 says behind and 3 says both.

Bill
When all else fails, get a bigger hammer.

Brian

Bill,

Thanks for that - I would never have come up with that! Wondered what 'UNION' was for...

Brian

billhsln

Not the best use of UNION, but it does the job you are looking for.

Bill
When all else fails, get a bigger hammer.

LarryMc

Bill
Using your code above, how do I test to see if the result set  has a ,say a '2', entry since it will return all three types if they exist?
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Hope this helps, you will have to open the data base first:

stmt = "SELECT '1' FROM TABLE WHERE FIELD LIKE '%RABBIT' "
stmt += "UNION SELECT '2' FROM TABLE WHERE FIELD LIKE 'RABBIT%' "
stmt += "UNION SELECT '3' FROM TABLE WHERE FIELD LIKE '%RABBIT%'"
hStmt = dbExecSQL(pDB,stmt)
err = dbGetErrorCode(hStmt)
IF LEN(err)
IF err = "42S02"  /* No data */
i = 0
ELSE
MESSAGEBOX d1,"("+str$(__LINE__)+") Problem with SELECT " + err,"Error"
ENDIF
ELSE
WHILE dbGet(hStmt)
dbGetData(hStmt,1,RecType$)
' Do something with returned code
ENDWHILE
ENDIF
dbFreeSQL(hStmt)


This will do the select (using Access), then read all available records.

Bill
When all else fails, get a bigger hammer.

Brian

Thanks, Bill,

An example is always useful! Wondered how I was going to string it all together...

Brian

LarryMc

I took one of the IWBasic examples and modified it.
I had a couple of issues:
1)
with '1','2' and '3' I couldn't get it to work
when I changed all three to * then I got some results.

2) In my code, if I lchanged the
dbGetData(hStmt,3,RecType$)to
dbGet(hStmt)
it would return only a partial list; don't know why.

Anyway, the following is my test program and it you comment out the 2nd and/or 3rd query lines you can see the different results that are returned.
As it is shown it will return all the records because of the entries I made in the database.

The database file is attached.
OPENCONSOLE

DEF first,last,street,city,state,zip,stmt,RecType$ as STRING
DEF address_ID,indtemp as INT

pdb = dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH + "db1.mdb","")

IF pdb <> NULL
PRINT "Connection established to " + GETSTARTPATH + "db1.mdb"

PRINT
PRINT "Data :"
PRINT

stmt = "SELECT * FROM Addresses WHERE LastName LIKE '%RABBIT' "
stmt += "UNION SELECT * FROM Addresses WHERE LastName LIKE 'RABBIT%' "
stmt += "UNION SELECT * FROM Addresses WHERE LastName LIKE '%RABBIT%'"
hstmt = dbExecSQL(pdb,stmt)
error = dbGetErrorCode(hstmt)
IF LEN(error)
PRINT
PRINT "Error Code: ", error
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
ENDIF

IF hStmt
dbBindVariable(hstmt,1,Address_ID)
dbBindVariable(hstmt,2,first)
dbBindVariable(hstmt,3,last)
WHILE dbGet(hstmt)
dbGetData(hStmt,3,RecType$)
PRINT "ID:",Address_ID
PRINT first," ",last
PRINT
ENDWHILE
dbFreeSQL(hstmt)
ENDIF

dbDisconnect(pdb)
ELSE
PRINT "Unable to connect to database"
ENDIF

PRINT "Press any key to exit"

waitcon
CLOSECONSOLE
END

LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

The following works for me, but comes up with unexpected results.  If you get 1,2,3 the RABBIT is alone, 1,3 stuff is in front of RABBIT, 2,3 stuff is behind RABBIT and 3 alone stuff is on both sides of RABBIT.

OPENCONSOLE

DEF last,RecType$ as STRING
DEF stmt[500]:ISTRING
DEF addressID as INT
DEF pdb,hStmt:POINTER

pdb = dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH + "db1.mdb",";")

IF pdb <> NULL
PRINT "Connection established to " + GETSTARTPATH + "db1.mdb"

PRINT
PRINT "Data :"
PRINT

stmt = "SELECT AddressID,LastName,'1' FROM Addresses WHERE LastName LIKE '%RABBIT' "
stmt += "UNION SELECT AddressID,LastName,'2' FROM Addresses WHERE LastName LIKE 'RABBIT%' "
stmt += "UNION SELECT AddressID,LastName,'3' FROM Addresses WHERE LastName LIKE '%RABBIT%';"
hstmt = dbExecSQL(pdb,stmt)
error = dbGetErrorCode(hstmt)
IF LEN(error)
PRINT
PRINT "Error Code: ", error
PRINT "Error Stmt: ", stmt
PRINT "Error Leng: ", len(stmt)
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
ELSE
WHILE dbGet(hstmt)
dbGetData(hStmt,1,AddressID)
dbGetData(hStmt,2,last)
dbGetData(hStmt,3,RecType$)
PRINT "ID:",AddressID," - ",last," ",RecType$
PRINT
ENDWHILE
dbFreeSQL(hstmt)
ENDIF

dbDisconnect(pdb)
ELSE
PRINT "Unable to connect to database"
ENDIF

'PRINT "Press any key to exit"

'waitcon
CLOSECONSOLE
END


Not what I would have expected.  I was expecting 1, for in front, 2 in back and 3 on both sides.

Weird,
Bill
When all else fails, get a bigger hammer.