Implicit (and Therefore Perhaps Unexpected) Conversions

I was surprised this morning when I saw a colleague post a message in the MSDN T-SQL Forum where he passed a numeric column as the first argument to the RIGHT() function. In other words, something like this:

declare @t table (IntegerColumn int)
insert @t select 12345
union all select 987
union all select 23
select LastDigit=right(IntegerColumn,1) from @t
/*
LastDigit
---------
5
7
3
*/

Huh? I didn’t know you could do that!

If you look at Books Online for the RIGHT() function, it 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."

Hmmm... So it specifically states that it “can be of any data type” and will be “implicitly converted to varchar”.

Intrigued, I wondered if other string functions did the same thing.

The same information regarding “implicit conversion” is not in the documentation for several of the other string functions; however, with a couple exceptions, they all perform the implicit conversion, as you can see in my findings below (using SQL2005):

select left(12345,2)            --Returns '12'
select left($273.56,4)          --Returns '273.'
select right(987.510,4)         --Returns '.510'
select right(1.23e10,5)         --Returns 'e+010'
select substring(12345,3,2)     --ERROR: Data Type INT is invalid for argument 1
  
select len(12345)               --Returns 5
select len(1e10)                --Returns 6 (Because 1e10 converts to '1e+010')
  
select lower(12345)             --Returns '12345'
select lower(1.23e10)           --Returns '1.23e+010'
select upper(1.23e10)           --Returns '1.23E+010'
select ltrim(12345)             --Returns '12345'
select rtrim(12345)             --Returns '12345'
  
select ascii(12345)             --Returns 49, the ASCII value of '1'
select unicode(12345)           --Returns 49, the UNICODE value of '1'
  
select replace(12345,2,9)       --Returns '19345'
select stuff(12345,3,1,9)       --Returns '12945'
  
select replicate(23,3)          --Returns '232323'
select reverse(123.45)          --Returns '54.321'
  
select charindex(4,543210)      --ERROR: Data Type INT is invalid for argument 1
select charindex('4',543210)    --Returns 2
select patindex('[0-9]%',12345) --ERROR: Data Type INT is invalid for argument 2
  
select soundex(12345)           --Returns '0000'
select difference(12345,98765)  --Returns 4, meaning least possible SOUNDEX difference
  
select quotename(12345)         --Returns '[12345]'

Just for kicks, what about datetime?:

declare @dt datetime, @dt2 datetime
set @dt='2009-08-15 11:15:00'
set @dt2='2009-01-31'
  
select left(@dt,6)          --Returns 'Aug 15'
select right(@dt,9)         --Returns '9 11:15AM'
select substring(@dt,5,2)   --ERROR: Data Type DATETIME is invalid for argument 1
  
select len(@dt)             --Returns 19
select lower(@dt)           --Returns 'aug 15 2009 11:15am'
select upper(@dt)           --Returns 'AUG 15 2009 11:15AM'
select ltrim(@dt)           --Returns 'Aug 15 2009 11:15AM'
select rtrim(@dt)           --Returns 'Aug 15 2009 11:15AM'
  
select ascii(@dt)           --Returns 65, the ASCII value of 'A'
select unicode(@dt)         --Returns 65, the UNICODE value of 'A'
  
select replace(@dt,15,99)   --Returns 'Aug 99 2009 11:99AM'
select stuff(@dt,5,2,@dt2)  --Returns 'Aug Jan 31 12:00AM 2009 11:15AM'
  
select replicate(@dt,2)     --Returns 'Aug 15 2009 11:15AMAug15 2009 11:15AM'
select reverse(@dt)         --Returns 'MA51:11 9002 51 guA'
  
select charindex('5',@dt)   --Returns 6
select patindex('A%',@dt)   --ERROR: Data Type DATETIME is invalid for argument 2
  
select soundex(@dt)         --Returns 'A200'
select soundex(@dt2)        --Returns 'J500'
select difference(@dt,@dt2) --Returns 2
  
select quotename(@dt)       --Returns '[Aug 15 2009 11:15AM]'

Is this a good thing, or a bad thing? I haven’t decided yet, though I’m leaning more towards “bad thing”. I thought T-SQL was a little more strongly-typed than this. Anyway, keep this knowledge in the back of your head and try not to accidentally use a numeric or datetime column in a string function in your coding.