MS SQL like operator

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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!
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
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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:

	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!

-- 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

Scott PletcherSenior DBACommented:
Depends on whether you want any/all of:
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.  
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.
Concur.  Sorry I was too lazy to read who had mentioned the extra matches.
Scott PletcherSenior DBACommented:

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

"Transportation Enterprises"

How does the application connect to the database? Is it through jdbc, odbc, etc?
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
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' + %
Sorry, I just recalled that you want "rans" to return nothing.  If that's still true, removing the whitespace won't fix your issue.

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 :)
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.
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?
By driver type, I meant things like "sql native client", "sql server", "sql server native client 10.0".
Happy Monday ben9:

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

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.

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
Ah, I didn't see the space after %, in '% '. Sorry for the noise.

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.
xav056Author Commented:
I do not think there is a solution for this tha was my initial fear.
Thanks foir your inputs
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

From novice to tech pro — start learning today.