Skip to content

Instantly share code, notes, and snippets.

@olivier-spinelli
Last active December 12, 2015 01:29
Show Gist options
  • Save olivier-spinelli/4691785 to your computer and use it in GitHub Desktop.
Save olivier-spinelli/4691785 to your computer and use it in GitHub Desktop.
How are T-Sql literals parsed?
select [ ]=1, [Val] = '0', Type = cast(sql_variant_property(0,'BaseType') as varchar(20)), [Precision]=cast(sql_variant_property(0,'Precision') as varchar(20)), [Scale]= cast(sql_variant_property(0,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(0,'MaxLength') as varchar(20))
union
select 2, '35', cast(sql_variant_property(35,'BaseType') as varchar(20)), cast(sql_variant_property(35,'Precision') as varchar(20)), cast(sql_variant_property(35,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(35,'MaxLength') as varchar(20))
union
select 3, '''str''', cast(sql_variant_property('str','BaseType') as varchar(20)), cast(sql_variant_property('str','Precision') as varchar(20)), cast(sql_variant_property('str','Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property('str','MaxLength') as varchar(20))
union
select 4, 'N''str''', cast(sql_variant_property(N'str','BaseType') as varchar(20)), cast(sql_variant_property(N'str','Precision') as varchar(20)), cast(sql_variant_property(N'str','Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(N'str','MaxLength') as varchar(20))
union
select 5, '''2013-01-29 18:43:02.030''', cast(sql_variant_property('2013-01-29 18:43:02.030','BaseType') as varchar(20)), cast(sql_variant_property('2013-01-29 18:43:02.030','Precision') as varchar(20)), cast(sql_variant_property('2013-01-29 18:43:02.030','Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property('2013-01-29 18:43:02.030','MaxLength') as varchar(20))
union
select 6, '.545878', cast(sql_variant_property(.545878,'BaseType') as varchar(20)), cast(sql_variant_property(.545878,'Precision') as varchar(20)), cast(sql_variant_property(.545878,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(.545878,'MaxLength') as varchar(20))
union
select 7, '1.22154', cast(sql_variant_property(1.22154,'BaseType') as varchar(20)), cast(sql_variant_property(1.22154,'Precision') as varchar(20)), cast(sql_variant_property(1.22154,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(1.22154,'MaxLength') as varchar(20))
union
select 8, '122.154', cast(sql_variant_property(122.154,'BaseType') as varchar(20)), cast(sql_variant_property(122.154,'Precision') as varchar(20)), cast(sql_variant_property(122.154,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(122.154,'MaxLength') as varchar(20))
union
select 9, '1.221544e2', cast(sql_variant_property(1.221544e2,'BaseType') as varchar(20)), cast(sql_variant_property(1.221544e2,'Precision') as varchar(20)), cast(sql_variant_property(1.221544e2,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(1.221544e2,'MaxLength') as varchar(20))
union
select 10, '54.0e-4', cast(sql_variant_property(54.0e-4,'BaseType') as varchar(20)), cast(sql_variant_property(54.0e-4,'Precision') as varchar(20)), cast(sql_variant_property(54.0e-4,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(54.0e-4,'MaxLength') as varchar(20))
union
select 11, '0x45FB41', cast(sql_variant_property(0x45FB41,'BaseType') as varchar(20)), cast(sql_variant_property(0x45FB41,'Precision') as varchar(20)), cast(sql_variant_property(0x45FB41,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(0x45FB41,'MaxLength') as varchar(20))
union
select 12, '0x45', cast(sql_variant_property(0x45,'BaseType') as varchar(20)), cast(sql_variant_property(0x45,'Precision') as varchar(20)), cast(sql_variant_property(0x45,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(0x45,'MaxLength') as varchar(20))
union
select 13, '0x12F', cast(sql_variant_property(0x12F,'BaseType') as varchar(20)), cast(sql_variant_property(0x12F,'Precision') as varchar(20)), cast(sql_variant_property(0x12F,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(0x12F,'MaxLength') as varchar(20))
union
-- Max number of numeric digits (precision) is 38 since Sql Server 2005.
select 14, '54654687898111111797989899898965465465', cast(sql_variant_property(54654687898111111797989899898965465465,'BaseType') as varchar(20)), cast(sql_variant_property(54654687898111111797989899898965465465,'Precision') as varchar(20)), cast(sql_variant_property(54654687898111111797989899898965465465,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(54654687898111111797989899898965465465,'MaxLength') as varchar(20))
order by 1;
-- From MSDN:
-- The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
--
declare @b bit, @c bit, @d bit;
set @b = 'TRUE';
set @c = 'FALSE';
-- Msg 245, Level 16, State 1, Line 4
-- Conversion failed when converting the varchar value 'NIMP' to data type bit.set @d = 'NIMP';
-- set @d = 'NIMP';
select @b, @c, @d;
--> 1, 0, NULL
select [ ]=1, [Val] = N'฿45.12', Type = cast(sql_variant_property(฿45.12,'BaseType') as varchar(20)), [Precision]=cast(sql_variant_property(฿45.12,'Precision') as varchar(20)), [Scale]= cast(sql_variant_property(฿45.12,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(฿45.12,'MaxLength') as varchar(20))
union
select 2, N'₣35', cast(sql_variant_property(₣35,'BaseType') as varchar(20)), cast(sql_variant_property(₣35,'Precision') as varchar(20)), cast(sql_variant_property(₣35,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(₣35,'MaxLength') as varchar(20))
union
-- Space characters can exist (but not tab nor comments not new lines)...
select 3, N'₣ 35', cast(sql_variant_property(₣ 35,'BaseType') as varchar(20)), cast(sql_variant_property(₣ 35,'Precision') as varchar(20)), cast(sql_variant_property(₣ 35,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(₣ 35,'MaxLength') as varchar(20))
union
--... and in this case, the unary minus must be handled (there must be no space between the minus and the first digit) :-(
select 4, N'₣ -35', cast(sql_variant_property(₣ -35,'BaseType') as varchar(20)), cast(sql_variant_property(₣ -35,'Precision') as varchar(20)), cast(sql_variant_property(₣ -35,'Scale') as varchar(20)), [MaxLength] = cast(sql_variant_property(₣ -35,'MaxLength') as varchar(20))
-- I'm not in a hurry to see the internal code of T-Sql parser... looks like quite different code paths coexist :-):
-- This works as if it was select ₣-35 as e42, 87
select ₣ -35e42, 87
-- But this does not: Money is not a float.
declare @p Money = ₣ -35e4, 87
select 0x0024, nchar(0x0024)
union select 0x00A2, nchar(0x00A2)
union select 0x00A3, nchar(0x00A3)
union select 0x00A4, nchar(0x00A4)
union select 0x00A5, nchar(0x00A5)
union select 0x09F2, nchar(0x09F2)
union select 0x09F3, nchar(0x09F3)
union select 0x0E3F, nchar(0x0E3F)
union select 0x17DB, nchar(0x17DB)
union select 0x20A0, nchar(0x20A0)
union select 0x20A1, nchar(0x20A1)
union select 0x20A2, nchar(0x20A2)
union select 0x20A3, nchar(0x20A3)
union select 0x20A4, nchar(0x20A4)
union select 0x20A5, nchar(0x20A5)
union select 0x20A6, nchar(0x20A6)
union select 0x20A7, nchar(0x20A7)
union select 0x20A8, nchar(0x20A8)
union select 0x20A9, nchar(0x20A9)
union select 0x20AA, nchar(0x20AA)
union select 0x20AB, nchar(0x20AB)
union select 0x20AC, nchar(0x20AC)
union select 0x20AD, nchar(0x20AD)
union select 0x20AE, nchar(0x20AE)
union select 0x20AF, nchar(0x20AF)
union select 0x20B0, nchar(0x20B0)
union select 0x20B1, nchar(0x20B1)
union select 0x20B9, nchar(0x20B9) -- New Indian Rupee Symbol (not yet supported in Sql Server 2012).
union select 0xFDFC, nchar(0xFDFC)
union select 0xFE69, nchar(0xFE69)
union select 0xFF04, nchar(0xFF04)
union select 0xFFE0, nchar(0xFFE0)
union select 0xFFE1, nchar(0xFFE1)
union select 0xFFE5, nchar(0xFFE5)
union select 0xFFE6, nchar(0xFFE6)
select 10 + - ++ - -20
select 10 + - ++ --20
declare @i000 varbinary(10) = 0x1007878;
declare @i00 varbinary(10) = 0x00;
declare @i0 varbinary(10) = 0x0;
declare @i varbinary(10) = 0x;
select @i000, DATALENGTH(@i000)
union all
select @i00, DATALENGTH(@i00)
union all
select @i0, DATALENGTH(@i0)
union all
select @i, DATALENGTH(@i);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment