Wildcard Searches
-
h.tobisch (11/18/2016)
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 ?
I'm sorry, I haven't edited the article to include that part. It's basically an issue on good practices as using functions on a column will prevent (in most cases) the use of indexes, while using LIKE might allow to use them. I showed this previously on this thread.
There's no real advantage on using the functions to determine if a pattern exists or not. However, they're very useful for determining the position of the pattern for additional work.
-
Iwas Bornready (11/21/2016)
I enjoyed reading your article, reminding me of some of the odd nuances of pattern matching.Thank you for the feedback.
If you have some interesting examples, it would be nice if you share them.
-
I'm using SSMS 2014 and get a slightly different execution plan than you. I copied your code.
- knotmyline - Tuesday, January 17, 2017 11:18 PM
I'm sorry, I made 2 mistakes in here.
First mistake (query B) is on filtering by a column with no index. To have an index seek, we need an index which key starts with the phone column.
So, let's create a nonclustered index on the phone column.CREATE INDEX IX_LIKETest_Phone ON dbo.LIKETest(phone);
If I run the same query, the results will be the same., but if I limit the number of columns, we'll be able to benefit from the index.
SELECT *
FROM dbo.LIKETest
WHERE phone LIKE '917%';
SELECT Name, phone
FROM dbo.LIKETest
WHERE phone LIKE '917%';The second mistake was making query D non SARGable by including an implicit data type conversion of the column going from varchar to nvarchar. We just need to remove the N at the start of the string to make it non-unicode and prevent the conversion.
SELECT *
FROM dbo.LIKETest
WHERE Name LIKE '[CDW]%';Thank you for pointing this out. I'll try to correct them.
-
Nice article and primer for string searching. For the decimal test, you could also use the try_parse to find records that can be converted to decimals. SELECT * FROM dbo.LIKETest WHERE TRY_PARSE(amount as decimal) is not null.
- jason.baker.sd - Friday, February 10, 2017 11:31 AM
Sure, but what's the fun on that? 😀
Seriously, this was written 2 years ago and there were much more places without 2012+ versions installed, so they wouldn't have this option available. I agree that using TRY_PARSE(), TRY_CAST() or TRY_CONVERT() are cleaner options. -
Thanks for this article!
I have one question based on creating the sample data. I got this warning when I ran the code:
Warning! The maximum key length is 900 bytes. The index 'IX_LIKETest' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.
(14 row(s) affected)
Do you get this warning, too? I figure it doesn't affect anything regarding the wildcard topic you discussed, but I am just curious about this error message.
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html - webrunner - Tuesday, February 14, 2017 1:28 PM
That's normal. I created the sample data table with a bad design. An index should not have a key length greater than 900 bytes, and the column used as the key has a length of 1000. With the data used here it won't fail, but this should be considered in real life implementations.
- Luis Cazares - Tuesday, February 14, 2017 1:34 PM
Thanks for the info, Luis!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply