standardbluecaboose (9/29/2015)
3 comments:
SELECT *FROM dbo.LIKETest
WHERE ' ' + Name + ' ' LIKE '%[^A-Za-z]Richard[^A-Za-z]%';
That is insane.
I'm not sure if this is good or bad.
'[1-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'
TIL 9000-19-39 is a valid date.
That's why it's referred as a basic validation. A single pattern can't validate for every single date, especially when dealing with Feb-29 on leap years. I used one validation in the past that would even check if the date was YYYY/MM/DD, DD/MM/YYYY or MM/DD/YYYY with some priority over the last two formats. That's only for data cleansing and starting on SQL 2012 we can forget about all this by using TRY_CONVERT()
Some people use the PATINDEX or CHARINDEX (which doesn’t allow wildcards) functions to validate if a string contains certain characters. That’s a bad idea and it’ll be discussed further in this article.
Did I miss the part where the author discusses why it's a bad idea? Did I skim over it?
I'm sorry, I did forget about this part.
It's actually a simple set of good habits to allow an index seek whenever possible. A few examples in here:
SELECT *
FROM dbo.LIKETest
WHERE Name LIKE 'B%'; -- SARGable, Uses Index Seek
SELECT *
FROM dbo.LIKETest
WHERE CHARINDEX( 'B', Name) = 1; -- non SARGable, Uses Index Scan
SELECT *
FROM dbo.LIKETest
WHERE LEFT( Name, 1) = 'B'; -- non SARGable, Uses Index Scan
SELECT *
FROM dbo.LIKETest
WHERE Name LIKE '[A-D]%'; -- SARGable, Uses Index Seek
SELECT *
FROM dbo.LIKETest
WHERE PATINDEX( '[A-D]%', Name) = 1; -- non SARGable, Uses Index Scan