February 18, 2014 at 1:45 pm
We have some records that have a binary FDFF at the end of a nvarchar column. It shows up as a diamond with a question mark in it. How can we remove this?
February 18, 2014 at 1:49 pm
Could you use REPLACE? Or would that be a problem with your real data?
February 18, 2014 at 2:04 pm
I've tried the REPLACE but I can't seem to get it to work. I have tried REPLACE(POSTCODE,N'<diamond ?>','') and REPLACE(POSTCODE,NCHAR(65533),'') and neither of those seem to work. Is there a better way to do it?
February 18, 2014 at 3:09 pm
Are you sure is NCHAR(65533)?
Here's something you can use to identify it.
WITH E1(N) AS(
SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))e(N)
),
E2(N) AS(
SELECT a.N FROM E1 a, E1 b
),
E4(N) AS(
SELECT a.N FROM E2 a, E2 b
),
cteTally(N) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) N
FROM E4
)
SELECT YourColumn, SUBSTRING(YourColumn, N, 1), UNICODE( SUBSTRING(YourColumn, N, 1))
FROM YourTable f
JOIN cteTally t ON LEN(f.YourColumn) >= t.N
ORDER BY YourColumn, N
February 18, 2014 at 4:37 pm
I tried out your script and it work great, thanks. And yes, it is a NCHAR(65533) that it shows at the end of our data.
February 18, 2014 at 4:46 pm
I'm not sure what's happening here. Maybe someone else can explain this.
It seems that you can correct this using a binary collation in the replace.
with sampledata as (
SELECT N'Something' + NCHAR(65533) AS POSTCODE )
SELECT POSTCODE , REPLACE(POSTCODE COLLATE Latin1_General_BIN, NCHAR(65533), N'')
FROM sampledata
February 19, 2014 at 1:43 pm
That worked, the collate seems to make everything behave as expected. But I'm with you, it would be nice if someone could explain it.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply