June 11, 2013 at 2:45 pm
Hi Guys,
How would I go about finding a specific "word" in a field?
What is the best way of returning records containing only the word "LC" and where LC stands on its own and is not part of another word \ acronym?
ie
LC,AMB
LCAND, CMB
ABC, LC
LC
Welcome
So the records returned would be:
LC,AMB
ABC, LC
LC
Thanks
Don
June 11, 2013 at 2:54 pm
I guess using wildcard character..
like
select * from student where studentname like 'LC%' ;
is this the one what you are looking for?
June 11, 2013 at 3:06 pm
Learner44 (6/11/2013)
I guess using wildcard character..like
select * from student where studentname like 'LC%' ;
is this the one what you are looking for?
You have the right idea, but you're not completely correct.
This query isn't SARGable, meaning that it won't let you use indexes to obtain the best performance, but it should give you the correct results.
SELECT *
FROM( VALUES('LC,AMB'),('LCAND, CMB'),('ABC, LC'),('LC'),('Welcome')) x(String)
WHERE ' ' + String + ' ' LIKE '%[ ,]LC[ ,]%'
More info on http://msdn.microsoft.com/en-us/library/ms179859.aspx
June 11, 2013 at 3:09 pm
great solution.
June 11, 2013 at 3:12 pm
Learner44 (6/11/2013)
I guess using wildcard character..like
select * from student where studentname like 'LC%' ;
is this the one what you are looking for?
Thanks for the reply
If only it were that easy.
Using wildcards in that way would return rows similar to the ones below, which I dont want.
LCAND, CMB
Welcome
"LC" should be a separate word \ acronym that stands on its own, with no alphanumeric chars directly before or after it.
June 11, 2013 at 3:21 pm
Luis Cazares (6/11/2013)
You have the right idea, but you're not completely correct.
This query isn't SARGable, meaning that it won't let you use indexes to obtain the best performance, but it should give you the correct results.
SELECT *
FROM( VALUES('LC,AMB'),('LCAND, CMB'),('ABC, LC'),('LC'),('Welcome')) x(String)
WHERE ' ' + String + ' ' LIKE '%[ ,]LC[ ,]%'
More info on http://msdn.microsoft.com/en-us/library/ms179859.aspx
Wow, that looks really promising.
I'll have a read of the link and convert the code tomorrow morning.
Luis, thanks a lot. 🙂
June 11, 2013 at 3:24 pm
You might miss some desired results if you have other delimiters such as tabs or slashes.
Be sure to add them between the brackets[] or to replace them with a space or comma. 😉
June 11, 2013 at 3:27 pm
Or you could use the DelimitedSplit8K.
SELECT *
FROM( VALUES('LC,AMB'),('LCAND, CMB'),('ABC, LC'),('LC'),('Welcome')) x(String)
cross apply dbo.DelimitedSplit8K(replace(String, ' ', ''), ',') s
where s.Item = 'LC'
You can find that code by following the article in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2013 at 3:30 pm
Sean Lange (6/11/2013)
Or you could use the DelimitedSplit8K.
I was going to suggest that as well 😀
June 11, 2013 at 3:38 pm
Just to clarify, but that query is SARGable and could use an index.
http://www.sql-server-performance.com/2007/t-sql-where/2/
If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an available index to perform the query, speeding performance and reducing the load on SQL Server.
edit: err, disregard. I was looking at the wrong query. Move along. 🙂
June 11, 2013 at 3:45 pm
Deque (6/11/2013)
Luis Cazares (6/11/2013)
Learner44 (6/11/2013)
I guess using wildcard character..like
select * from student where studentname like 'LC%' ;
is this the one what you are looking for?
You have the right idea, but you're not completely correct.
This query isn't SARGable, meaning that it won't let you use indexes to obtain the best performance, but it should give you the correct results.
Just to clarify, but that query is SARGable and could use an index.
http://www.sql-server-performance.com/2007/t-sql-where/2/
If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an available index to perform the query, speeding performance and reducing the load on SQL Server.
But I'm using a wildcard as the leading character and it won't allow SQL Server to use an index.
if the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a scan must be run, reducing performance and taking more time.
Maybe I wasn't clear to stablish that the non-SARGable query was the one I proposed. The one proposed by Learner44 is indeed SARGable but won't give the correct results as stated by Don.
June 11, 2013 at 3:46 pm
Luis Cazares (6/11/2013)
You might miss some desired results if you have other delimiters such as tabs or slashes.Be sure to add them between the brackets[] or to replace them with a space or comma. 😉
Yeah, I saw that, thanks.
I'll check the table tomorrow, but a space or comma should cover it.
Sean Lange (6/11/2013)
Or you could use the DelimitedSplit8K.
SELECT *
FROM( VALUES('LC,AMB'),('LCAND, CMB'),('ABC, LC'),('LC'),('Welcome')) x(String)
cross apply dbo.DelimitedSplit8K(replace(String, ' ', ''), ',') s
where s.Item = 'LC'
You can find that code by following the article in my signature about splitting strings.
Thanks for the suggestion, I'll take a look that as well, but I think I'll be using Luis' suggestion.
I seem to recall using your sig ages ago for ideas on a pivot query.
June 11, 2013 at 3:59 pm
Luis Cazares (6/11/2013)
But I'm using a wildcard as the leading character and it won't allow SQL Server to use an index.if the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a scan must be run, reducing performance and taking more time.
Maybe I wasn't clear to stablish that the non-SARGable query was the one I proposed. The one proposed by Learner44 is indeed SARGable but won't give the correct results as stated by Don.
D'oh! Sorry. You're absolutely right. I misinterpreted which query you were referring to. Figures one of the few times I decide to post to share something that I learned here and I would use it wrong. I've edited my post to reflect my mistake. 🙂
June 11, 2013 at 4:07 pm
Everyone makes mistakes, so continue to participate in this forum, that way we all win and learn. 😉
June 12, 2013 at 3:50 am
Hi Guys,
Any suggestions as to what Im doing wrong?
The script below isnt returning any results:
Select top 100 *
From CONTSUPP
Where RECTYPE = 'C'
AND CONTSUPREF like '%[ ,]LC[ ,]%'
Whereas this script:
Select top 100 *
From CONTSUPP
Where RECTYPE = 'C'
AND CONTSUPREF like '%LC%'
is returning records containing:
LC
LCAND
ABC,LC
TST, LC
Both of these scripts are being run in the same query window.
The script that Luis suggested returns the results it should.
It appears the first script doesn't like the delimiters in the script.
Any suggestions as to why the first script isn't returning any results?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply