Last active
December 12, 2015 01:29
-
-
Save olivier-spinelli/4691785 to your computer and use it in GitHub Desktop.
How are T-Sql literals parsed?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select 10 + - ++ - -20 | |
select 10 + - ++ --20 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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