Post reply

T-SQL Like

  • July 8, 2013 at 1:50 am

    #1630334

    L' Eomot Inversé (7/7/2013)

    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. 😉

    I, too, was intrigued by this behaviour. I did some experimenting and found that using image for both arguments also results in an error, but using image as the first argument and verbinary(20) (probably other lengths too) as the second is evaluated. Apparently, there is some sort of undocumented behaviour for LIKE when the first argument is image. I could not find more in the limited time I had available, though...

    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/

  • July 9, 2013 at 3:44 am

    #1630707

    Good question. 🙂

  • July 25, 2013 at 9:28 am

    #1635787

    Hugo Kornelis (7/3/2013)

    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.

    +1

    The real value in this question is the explanation from this thread.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 18, 2013 at 7:35 pm

    #1674683

    Good explanation, thanks.

Viewing 4 posts - 16 through 18 (of 18 total)

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