SQL How clarify a weird char in a string

Question

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:

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

Yes to try to see what it is in COLLATE Latin1_General_100_BIN2 — Andres Urrego Angel, Feb 02 '18 at 15:55
`0x0D` is a carriage return `\r`, `(char)13`. Just use `SUBSTRING(0,8)` to get rid of it. Could be that you read the file as unix format expecting a `\n`, i.e. a new line character `(char)10` as line separator. — Olivier Jacot-Descombes, Feb 02 '18 at 16:05
@OlivierJacot-Descombes Done it goes thru, could you please post your answer to tag it as done :) thanks — Andres Urrego Angel, Feb 02 '18 at 16:14
NVARCHAR means UTF-16 so every two bytes is a code unit. So, the question about 0D 00 (written as '\u000D' in some languages, which is the single code unit for the U+000D [Unicode](http://www.unicode.org/charts/nameslist/index.html) codepoint.) — Tom Blodget, Feb 02 '18 at 17:02

Olivier Jacot-Descombes · Accepted Answer · 2018-02-02T16:51:09.740

0

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).

edited Feb 02 '18 at 16:51

answered Feb 02 '18 at 16:35

Olivier Jacot-Descombes

104,806
13
138
188

SQL How clarify a weird char in a string

1 Answers1