I am new to string handling in SQL server and was wondering what makes the below code return the result it returns. Could I please have a detailed explanation?
My code:
Select CAST(CAST(CAST('08' as nvarchar) as varbinary) as varchar) as [result] Output:
| Result |
| 0 |
What is happening here from '08' being converted to nvarchar to varbinary to varchar??
SELECT CAST('08' AS varbinary), result is 0x3038, this is ASCII code or ANSI code.
SELECT CAST(N'08' AS varbinary), CAST(CAST('08' AS nvarchar) AS varbinary) result is 0x30003800 0x30003800, it's UNICODE, that means, when you CAST a char to nchar, SQL SERVER will change it's binary structure to fit character standards.
SELECT CAST(0x30003800 AS varchar), it will return 0, because the first byte 0x30 is ASCII char '0', the next byte 0x00 is ASCII char 'null', it cannot be shown and always be treat as a control symbol means end of string, so the display will be cutoff. Add another CAST outter your expression, SELECT CAST(CAST(CAST( CAST('08' as nvarchar) AS varbinary) AS varchar) AS varbinary), you will see 0x30003800, every bytes you convert from nvarchar are there.
What confuses you is the char to nchar conversion, which changes the binary data.
null character as an EoF marker, while others do not. For example, ADS displays 08.
nullcharacters.CAST('08' as nvarchar)does seem odd though; it could be abbreviated toN'08'.