Link to home
Start Free TrialLog in
Avatar of xav056
xav056

asked on

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
Avatar of DALSOM
DALSOM
Flag of United States of America image

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.
Avatar of Lowfatspread
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
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.
Avatar of tknudsen-qec
tknudsen-qec

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

Depends on whether you want any/all of:
,Trans
.Trans
)Trans
-Trans
/Trans
etc.
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.  
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.
:-).

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?
Avatar of xav056

ASKER

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.
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 :)
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".
SOLUTION
Avatar of tknudsen-qec
tknudsen-qec

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ah, I didn't see the space after %, in '% '. Sorry for the noise.

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xav056

ASKER

I do not think there is a solution for this tha was my initial fear.
Thanks foir your inputs