September 29, 2015 at 8:07 am
standardbluecaboose (9/29/2015)
Thanks for the quick reply.
The
WHERE ' ' + Name + ' ' LIKE '%[^A-Za-z]Richard[^A-Za-z]%';is insane in a bad way. I have no suggestions for how to make it better, but it feels like such a hack. However, a lot about SQL feels like a hack to me.Thank you for the update about the PATINDEX.
I will say that
Column LIKE '%X%'`and`PATINDEX('X', Column)both do a full scan.
Sure, it feels like a hack, but sometimes you have to do nasty things for the code to work.
And yes, the only way to get an index seek is when searching in the "Starts with" formula. By the way, your PATINDEX is missing something to work, it should be PATINDEX('%X%', Column)
September 29, 2015 at 2:06 pm
Luis Cazares (9/29/2015)
Alan.B (9/29/2015)
Great article Luis! Informative, to the point. I picked up a couple things.Interesting technique using REPLICATE, I would add that I like to use CHAR() more often in my searches. E.g CHAR(32) for spaces, CHAR(9) for tabs when I'm using LIKE, PATINDEX or CHARINDEX. Much easier to distinguish than the literal version of each.
There are lots of things that can be done with patterns, like a pattern based splitter.
That would make for a great article 😉
Interesting, I prefer the single space over the CHAR(32) but the CHAR(9) over the tab literal. Maybe I'd just have to get used to it.
About the article, you mean writing about the things that can be done? or the splitter? because the splitter already has an article written by Dwain.
Things that can be done. I've seen so many examples over the years and it seams like a great compliment to the original article. Was just thinking out loud.
-- Itzik Ben-Gan 2001
September 29, 2015 at 4:22 pm
Thanks for the article. This provides good background and some tweaks I didn't think of upon which I can build.
September 30, 2015 at 1:14 am
September 30, 2015 at 2:35 am
Hi Luis,
Another Approach for "Like" Search is to Use "DataLength" And "Replace" together, as shown below:
Begin
declare @lv_search_string varchar(100);
select @lv_search_string ='Parker'
SELECT * FROM dbo.LIKETest WHERE DATALENGTH(Name) <> DATALENGTH(REPLACE( NAME, @lv_search_string, '' ))
End
Go
Logic As Follows:
If search string is found in data, replacing it with empty String and then calculation of data length will never be equal to the actual Data Length of the Data.
This One Query supports 3 use cases:
--F) Ends with ‘some string
SELECT * FROM dbo.LIKETest WHERE Name LIKE '%Parker';
--G) Contains ‘some string’
SELECT * FROM dbo.LIKETest WHERE Name LIKE '%Richard%';
--H) Contains ‘exact word’
SELECT * FROM dbo.LIKETest WHERE Name LIKE '% Richard %';
I do not have the performance stats but,It was Better than Regular Like Search. Can Anyone Provide the stats using large volume of data..??
September 30, 2015 at 7:21 am
Thanks for the great refresher course on wildcards.
September 30, 2015 at 8:21 am
Peddi Praveen kumar (9/30/2015)
Hi Luis,Another Approach for "Like" Search is to Use "DataLength" And "Replace" together, as shown below:
Begin
declare @lv_search_string varchar(100);
select @lv_search_string ='Parker'
SELECT * FROM dbo.LIKETest WHERE DATALENGTH(Name) <> DATALENGTH(REPLACE( NAME, @lv_search_string, '' ))
End
Go
Logic As Follows:
If search string is found in data, replacing it with empty String and then calculation of data length will never be equal to the actual Data Length of the Data.
This One Query supports 3 use cases:
--F) Ends with ‘some string
SELECT * FROM dbo.LIKETest WHERE Name LIKE '%Parker';
--G) Contains ‘some string’
SELECT * FROM dbo.LIKETest WHERE Name LIKE '%Richard%';
--H) Contains ‘exact word’
SELECT * FROM dbo.LIKETest WHERE Name LIKE '% Richard %';
I do not have the performance stats but,It was Better than Regular Like Search. Can Anyone Provide the stats using large volume of data..??
I tested on a million row table with and without an index on the column and the performance was similar on both options. On some runs the LIKE would be faster and in others it would be slower. And I'm taking about 2% faster or slower.
On the other hand, that approach is only equivalent to the "Contains ‘some string’" approach. In the others, it would return incorrect results.
October 1, 2015 at 8:11 pm
This article is just another example of why lack of native "modern" regex support is holding back MS SQL.
replicate('_',fixedLength) ?
.{fixedLength}
.{min,max} ?
... like replicate('_',...) or ... like replicate ('_',...) or ... like replicate('_',...) ... ... ...
And that's the tip of the iceberg.
1/100th of the functionality is already there with LIKE patterns--make the leap to variable length and alternative sub-pattern matches already! (with a syntax that everyone should already be familiar with)
With an understanding of how regex matches are performed (greediness, possessiveness (PCRE), anchoring, etc.), many potential performance issues could be avoided. Those that can not be avoided are a problem wherever they arise.
October 3, 2015 at 12:27 pm
Thanks for your article.
the query for Contains ‘exact word’ will not work if the searched word is the last word on a line.
October 5, 2015 at 8:35 am
quagmired (10/1/2015)
This article is just another example of why lack of native "modern" regex support is holding back MS SQL.
You're probably right. I'm not sure what prevents MS to include regex support on SQL Server, but it would be great to have it without using CLR.
October 5, 2015 at 8:37 am
Yakov Shlafman (10/3/2015)
Thanks for your article.the query for Contains ‘exact word’ will not work if the searched word is the last word on a line.
Thank you, that's actually addressed in the article and shows the solution for that issue.
November 18, 2016 at 1:51 am
Great article. And I love the Sesame Street reference 😀
November 18, 2016 at 3:00 am
You say using CHARINDEX or PATINDEX for testing for the occurrence of a character string is a bad idea.
that's why i read this article.
Could not find more of an argument than trailing spaces, which is not much of an argument as trailing spaces hardly will contain the search string.
so, what's the reason ?
November 21, 2016 at 7:37 am
I enjoyed reading your article, reminding me of some of the odd nuances of pattern matching.
November 21, 2016 at 7:54 am
Christy M (11/18/2016)
Great article. And I love the Sesame Street reference 😀
I'm glad that you liked it. Thank you for the feedback.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply