T-SQL Like

  • kathiravan P

    Old Hand

    Points: 318

    Comments posted to this topic are about the item T-SQL Like

  • Iggy-SQL

    SSCarpal Tunnel

    Points: 4169

    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: 6098

    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

    Thanks for giving detailed explanation. ๐Ÿ™‚

  • Carlo Romagnano

    SSC-Insane

    Points: 22013

    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: 258985

    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: 64685

    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/

  • This was removed by the editor as SPAM

  • kapil_kk

    SSC-Insane

    Points: 21316

    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

    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

    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

    Great discussion. And thanks to Kathivaran who started it!

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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

    Nice......

  • TomThomson

    SSC Guru

    Points: 104773

    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

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

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