T-SQL Like

Question

T-SQL Like

kathiravan P

Old Hand

Points: 318
More actions
July 2, 2013 at 10:22 pm

#395076

Comments posted to this topic are about the item T-SQL Like
July 3, 2013 at 1:38 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Iggy-SQL SSCarpal Tunnel Points: 4169 More actions · Answer 1

To me, the explanation isn't quite right. I believe the right explanation should be something along this line:

Statement 1 : The SQL Database Engine converted the Val value to character string before comparing it with the LIKE condition, therefore it returns all row, because as we all know, % is the wildcard in LIKE condition.

Statement 2 : The SQL Database Engine tried to convert the character string '%' to float before it compares it with the value in Val column using the = operator, therefore the statement fails because '%' cannot be converted into float.

Urbis, an urban transformation company

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

Iggy-SQL (7/2/2013)
To me, the explanation isn't quite right. I believe the right explanation should be something along this line:
Statement 1 : The SQL Database Engine converted the Val value to character string before comparing it with the LIKE condition, therefore it returns all row, because as we all know, % is the wildcard in LIKE condition.
Statement 2 : The SQL Database Engine tried to convert the character string '%' to float before it compares it with the value in Val column using the = operator, therefore the statement fails because '%' cannot be converted into float.

+1

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

kathiravan P Old Hand Points: 318 More actions · Answer 3

kathiravan P

Old Hand

Points: 318

July 3, 2013 at 12:48 am

#1629370

Thanks for giving detailed explanation. 🙂

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 4

Iggy-SQL (7/2/2013)
To me, the explanation isn't quite right. I believe the right explanation should be something along this line:
Statement 1 : The SQL Database Engine converted the Val value to character string before comparing it with the LIKE condition, therefore it returns all row, because as we all know, % is the wildcard in LIKE condition.
Statement 2 : The SQL Database Engine tried to convert the character string '%' to float before it compares it with the value in Val column using the = operator, therefore the statement fails because '%' cannot be converted into float.

Of course!

+1 🙂

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

Nice question, but of a lacking explanation, but the thread makes up for it. 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

Iggy-SQL (7/2/2013)
To me, the explanation isn't quite right. I believe the right explanation should be something along this line:
Statement 1 : The SQL Database Engine converted the Val value to character string before comparing it with the LIKE condition, therefore it returns all row, because as we all know, % is the wildcard in LIKE condition.
Statement 2 : The SQL Database Engine tried to convert the character string '%' to float before it compares it with the value in Val column using the = operator, therefore the statement fails because '%' cannot be converted into float.

Better than the explanation in the question, but still incomplete. Why would SQL Server choose to do one conversion for the first statement and another conversion for the second?

Here's the answer.

For most operations that involve mixed data types, SQL Server uses the rules of data type precedence to determine the conversions needed. Float is higher than varchar, so SQL Server will try to convert the varchar to float. That's what happens in statement 2.

Statement 1 is different because the LIKE operator is used. When using operators that require a specific data type, operands are always directly converted to that data type. LIKE requires strings, so in this case the '%' is not converted (it's already string), and the float values are converted.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

Nice question and nice explanation from Iggy and Hugo 🙂

+1

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lokesh Vij SSChampion Points: 10836 More actions · Answer 8

Nice Question and in-line explanation makes it worthy 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Iggy-SQL (7/2/2013)
To me, the explanation isn't quite right. I believe the right explanation should be something along this line:
Statement 1 : The SQL Database Engine converted the Val value to character string before comparing it with the LIKE condition, therefore it returns all row, because as we all know, % is the wildcard in LIKE condition.
Statement 2 : The SQL Database Engine tried to convert the character string '%' to float before it compares it with the value in Val column using the = operator, therefore the statement fails because '%' cannot be converted into float.

A simpler correction would be just change the second "first" in the original explanation to "second". I suspect that's what Kathiravan intended, because mixing first and second is an easy slip to make.

Anyway, it's a good question. It neatly illustrates the fact that implicit conversion takes place in two distinct kinds of context: strong contexts, where a particular kind of type is mandatory as in the first statement where both arguments of LIKE are required to have a string type, and other contexts where which argument has to take what type is decided by the type precedence rules.

Tom

Revenant SSC-Forever Points: 42467 More actions · Answer 10

Great discussion. And thanks to Kathivaran who started it!

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

L' Eomot Inversé (7/3/2013)
[...]as in the first statement where both arguments of LIKE are required to have a string type[...]

Hi,

they are not required to be a string type. But they will be converted to string type, if that is possible.

Now, this "if that is possible" statement made me curious, so I have tested all data types.

The following cannot be used:

geography
geometry
hierarchyid
image
sql_variant
xml

But the error when using image datatype is a little misleading 🙂

Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.

SELECT 1 WHERE CONVERT(image, ' ') LIKE '%Test%'

Note: If you exchange the parameter positions (varchar LIKE image) , you get the correct error message.

Best Regards,

Chris Büttner

Bangla Hall of Fame Points: 3137 More actions · Answer 12

Bangla

Hall of Fame

Points: 3137

July 5, 2013 at 3:08 am

#1629987

Nice......

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Christian Buettner-167247 (7/4/2013)
L' Eomot Inversé (7/3/2013)
[...]as in the first statement where both arguments of LIKE are required to have a string type[...]
Hi,
they are not required to be a string type. But they will be converted to string type, if that is possible.
Now, this "if that is possible" statement made me curious, so I have tested all data types.

You can describe it that way, but then you have to explain why they are converted to string type. You also have to worry whether there are any types whose values can be converted to string type using "cast" or "convert" but can't be converted to string type through implicit conversion. It's far simpler to say "the aruments are required to be string type, and this will cause implicit conversion (and I'm pretty sure SQL will do the conversion before invoking the function, just as every compiler and interpreter I've ever seen the internals of would) where possible and an error where implicit conversion to the required type is impossible (that's a compile time error when the type system decrees it, and a run time error otherwise).

The following cannot be used:
geography
geometry
hierarchyid
image
sql_variant
xml
But the error when using image datatype is a little misleading 🙂
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.
SELECT 1 WHERE CONVERT(image, ' ') LIKE '%Test%'
Note: If you exchange the parameter positions (varchar LIKE image) , you get the correct error message.

Picking argument 2 instead of argument 1 does seem pretty bizarre. Exchanging the parameter positions is interesting: the error message that comes out is the same, but of course it is now correct. However, in at least some cases where parameter 1 has invalid type other than image and parameter 2's type is image the error message points at parameter 1. I'm not 100% sure that I could write that parsing bug if I tried, given a reasonable basic structure for the parser. 😉

Tom