Well well well…surprise surprise.. today I was working in SQL server and I passed the following statement:

SELECT LEFT (123,1) instead of SELECT LEFT (‘A123’,2), and surprisingly the statement SELECT LEFT(123,1) returned 1. Strange ha! I thought LEFT works with only string. Then checked what is given in the MSDN site for LEFT function: Well according to MSDN LEFT returns the left part of a character string with the specified number of characters.

 
And the syntax for the LEFT function is LEFT (character_expression, integer_expression)

MSDN states the following about the first argument: character_expression

“Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type (except text or ntext) that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.”

 

So it states in the above explanation that it implicitly converts the data to varchar or nvarchar.

 

Interesting right..so I wondered if the other string function would also work with integers..


Below you can see the result of my experiment: 

  •  
    • SELECT LEFT(102042,3)                                                 — returns 102
    • SELECT LEFT($102042,4)                                               — returns 1020
    • SELECT RIGHT(102042,3)                                              — returns 042
    • SELECT RIGHT(1e308,5)                                                 — returns e+308
    • SELECT LEN(102042)                                                       — returns 6
    • SELECT LEN(1e308)                                                          — returns 6, it is converted to 1e+308
    • SELECT LTRIM(102042)                                                 — returns 102042
    • SELECT RTRIM(102042)                                                 — returns 102042
    • SELECT ASCII(102042)                                                    — returns 49, the ASCII value of 1 
    • SELECT SUBSTRING(102042,1,3)             — ERROR: Argument data type int is invalid for argument 1
    • SELECT DIFFERENCE(10245,24565)     –returns 4, the least possible difference (it is not clear to me)
    • SELECT UPPER(1e308)                                                    — returns 1E+308
    • SELECT UPPER(12456)                                                    — returns 12456
    • SELECT LOWER(12456)                                                   — returns 12456
    • SELECT LOWER(1e308)                                                   — returns 1e+308
    • SELECT QUOTENAME(102042)                                    — returns [102042]
    • SELECT REPLICATE(102042,2)                                    — returns 102042102042
    • SELECT REVERSE(102042)                                            — returns 240201
    • SELECT STUFF(102042,2,3,555)                                   — returns 155542
  •  



Now I think in SQL, the code is parsed and compiled and the data type of a variable is set during the compilation time, so SQL compiler should know at COMPILE time whether the argument we are passing to the string function are string or not and it should give some kind of warning before it is executed. I thought SQL was little strongly type than this. So, developers keep this information in the back of your mind while coding..


Cheers,

Subhro Saha

Advertisements