March 26, 2004 at 6:43 am
I have a sql query that uses a % wildcard like so:
SELECT PIN, SCAN...etc... FROM PARCEL WHERE PIN LIKE ' " & variable & "%'"
so it's simply a search on a variable, appending the wildcard to the search each time. my problem is that it seems to be returning varied results. for example... if the value PB141565614CA is searched exactly as that, it returns the record fine. if I use 'PB1415656' (truncating the 14CA) it also returns a few records with the desired record included. now if I drop one more character, ie 'PB141565' (truncated 614CA) it returns a couple of records in the set, but not the desired ones anymore. ie, it may return a couple of records like PB141565923 , PB141565923, but not the desired range, and it is obviously not returning full results.
My wildcard seems to be working when the % only is searching for 4 characters or less... it seems that the wider my wildcard search, the less relevant records are returned. Any ideas are welcome as I am totally confused at this one.
March 26, 2004 at 8:56 am
Very Weird, I have been using like '%' in many queries and SPs but never ever encountered any such issue and it does not definitely limit searching to 4 characters or less. Have you tried running the same query from SQL Query Analyzer by passing the variable directly?
However try one thing append '_'(underscore) to your variable and give it a shot and see if it fetches the desired output. I know it makes no sense but since it is little weird I thought it may make a difference.
Thanks
Prasad Bhogadi
www.inforaise.com
March 26, 2004 at 10:37 am
Can you post some sql that reproduces this? Maybe then we can figure out what is happening. I've never had a problem using the % wildcard with a like statement.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 26, 2004 at 12:51 pm
I have not seen this issue. Did you validate the results in QA and verify the query itself is doing as expected?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply