MS SQL like operator

Hello
I have a really situation where the modification to code is limited
I would like to look for a word in column lets take the following two values in a column
"The best transportaion means is"
"Transportation is best by"

if user types in 'trans' I should be able to find both results
if they type in the whole word I should be qable to find it
if they tyoe "rans" no results should be returned

I have an application that submits this to a stored proc
I cant modify the stored proc so at this point this is what I have in the stored proc
@SearchTerm -->is a stored proc paramter
Select *
from table
where column like  @SearchTerm

The application does this now
if the user types in trans it takes it and appends % to it
so this retrieves the result only for columns starting with trans
but can't find this "The best transportaion means is"

if I do in the application '% ' + 'trans' + %
That will find "The best transportaion means is"
but wont find the first value
Any ideas how I can resolve this
LVL 9
xav056Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DALSOMCommented:
Hi,
you can rewrite the original Store Procedure and rename the original to *old, and in the new one, check if : its like sentence is written like this ' column1 like '%' + @searchstring + '%'

Ok, you only might do this , if you have permission to.

I hope this help you!
Bye
Dalsom.
0
LowfatspreadCommented:
your best bet is to investigate full text indexing probably... and  that wouldn't equate rans with transportation (i think) then...

for your current case possibly you should have a computed column which appends a space at the start and end of the text
and also replaces all punctuation with a space ',();@#+-&%£$"! etc  then if you do your like searches against this appending % space at the front and  space % at the end...

you're going to be doing a table scan anyway... the only problem in stripping punctuation would be names like O'Neil or hyphenated words  and numbers expressed as digits ... but if you replaced punctuation in the search term with a space as well
then probably that would be ok....



good luck
0
Scott PletcherSenior DBACommented:
Outside of full-text indexing, I suggest something like this:

column LIKE @search_string + '%'  OR
column LIKE '%[^a-z]' + @search_string + '%'

That way the string to be found would have to:
1) start the value
2) be after a non-letter character

If you want to include digits as part of a "word", then you can do this:
column LIKE '%[^a-z0-9]' + @search_string + '%'

I suggest not doing that.  That can miss legitimate matches, the earlier version might on occassion provide a false match.  I'd rather live with the false matches.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tknudsen-qecCommented:
Fun problem.  I'd do it a bit differently:
1) LIKE doesn't go well with @variables unless you "cheat" and build a new query with it (which can be messy).  I sometimes use CHARINDEX to work around this.

2) Using the above, its easy to add spaces to get the exact results you want.  Here's a test sample:

CREATE TABLE #tmpTest
(
	ID INT IDENTITY(1,1),
	TextLookup VARCHAR(500)
)

INSERT INTO #tmpTest (TextLookup) VALUES ('The best transportaion means is')
INSERT INTO #tmpTest (TextLookup) VALUES ('Transportation is best by')
INSERT INTO #tmpTest (TextLookup) VALUES ('This is a random sample string')
INSERT INTO #tmpTest (TextLookup) VALUES ('His dog has fleas')

--run some test queries!
--==========================================--
DECLARE @SearchTerm VARCHAR(50)

-- TEST1: TRANS gets two results, as expected
SET @SearchTerm = 'trans'
SELECT * FROM #tmpTest WHERE CHARINDEX(' ' + @SearchTerm, ' ' + TextLookup) >= 1

-- TEST2: RANS gets zero results, as expected
SET @SearchTerm = 'rans'
SELECT * FROM #tmpTest WHERE CHARINDEX(' ' + @SearchTerm, ' ' + TextLookup) >= 1

-- TEST3: HIS gets one result; "THIS" does not qualify as per requirement
SET @SearchTerm = 'his'
SELECT * FROM #tmpTest WHERE CHARINDEX(' ' + @SearchTerm, ' ' + TextLookup) >= 1

Open in new window

0
Scott PletcherSenior DBACommented:
Depends on whether you want any/all of:
,Trans
.Trans
)Trans
-Trans
/Trans
etc.
0
tknudsen-qecCommented:
In ScottPletchers example, all items would be omitted alas.  As stated by someone previously, I'd prefer to get the extra matches and skip the whitespace logic entirely.  
0
Scott PletcherSenior DBACommented:
That's what I stated, and that's why I did my logic as "non-letter" rather than as any specific character(s).  I'd rather get extra matches and sort/whatever to put the better ones first than miss a potential match.
0
tknudsen-qecCommented:
Concur.  Sorry I was too lazy to read who had mentioned the extra matches.
0
Scott PletcherSenior DBACommented:
:-).

Thought of a really good match you wouldn't want to lose:

"Transportation Enterprises"

0
ben9Commented:
How does the application connect to the database? Is it through jdbc, odbc, etc?
0
xav056Author Commented:
application connects to database using odbc
The solutions above do require modification to the stored procs which isn't possible.
I was hoping for a solution from the app itself
maybe with the like operator
0
tknudsen-qecCommented:
As far as I know, you still cannot do LIKE with a @variable.  Unless they changed that in 2008, or unless you're building a string and executing that.  Can you confirm? Otherwise you're in trouble if you cannot mod the SP :-)

Besides that, the only reason your first method doesn't work is that you have an extra whitespace rendering the first search-result invalid.

WONT WORK: '% ' + 'trans' + %
SHOULD WORK:  '%' + 'trans' + %
0
tknudsen-qecCommented:
Sorry, I just recalled that you want "rans" to return nothing.  If that's still true, removing the whitespace won't fix your issue.
0
tknudsen-qecCommented:
xav056:

Assuming you are building dynamic SQL to execute inside the SP, this would work for you: Its just  your original code with an extra clause:

'''% ' + @SearchTerm + '%'' OR Term LIKE ''' + @SearchTerm + '%'''

Note1: This will only work if you're building executable SQL in the stored procedure.

Note 2: The apostrophes are a little tricky.  There are NO double-quotes in my string.  You should be able to encapsulate the entire thing with double-quotes in .NET and pass it to your stored procedure.

Note 3: If you're dealing with very many rows (hundreds of thousands), I suspect indexes will have a very hard time helping you with this query. Something to keep an eye on :)
0
Scott PletcherSenior DBACommented:
You don't have to change the stored proc to get the main part of my search, just change the @SearchTerm:

Instead of @SearchTerm = '%'  + what_the_user_entered + '%'

Set @SearchTerm to '%[^a-z]' + what_the_user_entered + '%'

EXCEPT that you miss the value if it's the very first word of the column being searched.

To address that, you would need an OR in the WHERE clause.
0
ben9Commented:
Normally, like '% ' + 'trans' + % should find 'Transportation' as well as '... transportation'.

I wonder whether it's the ODBC that gives you the trouble. What is your application written in? Is it C#? Also, what is the driver in the odbc setup?

Also, is it possible that your application is adding something to the search parameter?
0
ben9Commented:
By driver type, I meant things like "sql native client", "sql server", "sql server native client 10.0".
0
tknudsen-qecCommented:
Happy Monday ben9:

'% ' + 'trans'
Won't find a field that STARTS with transportation is the issue.  The whitespace rules it out.

Cheers,
tk
0
Scott PletcherSenior DBACommented:
Exactly.  As I noted, my check of non-alphabetic is much more flexible than a space ... but it still cannot match the column actually starting with the search string; NO method I can think of except an OR could check for a delim around the search AND check for the column starting with that value.

It really is two different comparisons.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ben9Commented:
Ah, I didn't see the space after %, in '% '. Sorry for the noise.

0
ben9Commented:
This is not a good solution but I guess you could run the query twice one with '%'+@term+'%' and the other with @term+'%'

or get the result for '%'+@term+'%' and filter out the result in the application space,

since the application seems under your control.
0
xav056Author Commented:
I do not think there is a solution for this tha was my initial fear.
Thanks foir your inputs
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.