sqlsql-serversql-server-2008etlchars

SQL How clarify a weird char in a string

I have grabbed from a file source a column that suppose to be a DATE not more longer than 8 chars in a NVARCHAR(50) staging field. Now when I try to cast it to DATE it fails because SQL is not able to apply the transformation.

I tried to go deeper and understand what's going on and take a look the length. Among the remarkable things I realized that the len is always 9 and has at the end in VARBINARY 00D00. I added manually a new row how suppose to came the field and the len fit as I expect.

code:

SELECT  [LastPriceChange],len([LastPriceChange]), 
convert(varbinary(max),[LastPriceChange])
FROM  [STAGING].[MBEW]
group by [LastPriceChange]
order by 2 desc

Output:

enter image description here

I'm trying to get the final part to understand what is that thinking that is 00D00 but when I try :

SELECT REPLICATE(NCHAR(000D00), 5 COLLATE Latin1_General_100_BIN2)

It doesnt go thru, some one have any clue about how should I figure it out?

thanks

Solution

  • 0x0D is a carriage return '\r', (char)13. Just use SUBSTRING(LastPriceChange, 0, 8) to get rid of it.

    Could it be that you read the file as Unix format expecting only a '\n' (new line character, (char)10) as line separator instead of "\r\n" (carriage return + new line) as usual in Windows? This would explain why the 0x0D was left over.

    See: ASCII, Control characters (Wikipedia).