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
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
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
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.
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.
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:
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
Depends on whether you want any/all of:
,Trans
.Trans
)Trans
-Trans
/Trans
etc.
,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"
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?
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
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' + %
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 :)
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.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ah, I didn't see the space after %, in '% '. Sorry for the noise.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I do not think there is a solution for this tha was my initial fear.
Thanks foir your inputs
Thanks foir your inputs
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.