January 16, 2013 at 1:39 pm
Our content has been contaminated by UNICODE NULL characters in our SQL Server 2008 database that don't play nicely with our java libraries that depend on UTF8 characters.
I am trying to compose SQL statements that will identify the characters within the nVarchars and then replace with an empty value ''. The main problem I'm having is creating a check to see whether a particular character is a UNICODE NULL or not.
When I try to view the values in the database, they look like 'u' instead of 'username'. I suspect there is an invisible UNICODE NULL just after the first letter. My goal is to simply delete that second, invisible character (UNICODE NULL).
SELECT name
FROM users
WHERE name LIKE '%[^ -~]%' COLLATE Latin1_General_BIN
Returns the following:
B
M
M
N
S
S
S
S
ÿþA
select name, CAST(RIGHT(name,1) AS varbinary(128)) AS RIGHTER_1,
from users
where id=1
returns:
B 0x4200
Any ideas are greatly appreciated.
mtf
January 16, 2013 at 2:21 pm
something like this can help you identify the actual char;
i don't know what unicode null is (is that ASCII code(0)?
but this will help figure it out;
select
name,
N as Position,
SUBSTRING(name,N,1) As TheChar,
ASCII(SUBSTRING(name,N,1)) TheAsciiCode
from users
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL) -1) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n BETWEEN 0 AND 255
AND MiniTally.n < LEN(name)
ORDER BY name,N
Lowell
January 17, 2013 at 2:14 am
Hey, that's a pretty cool piece of SQL! Thanks for offering it.
I think the +1 helps it traverse the string, but honestly, this is using some structures I've never even seen before (CROSS APPLY -- I need to study up on that!), so I just took a guess on the +1....
select
name,
N as Position,
SUBSTRING(name,N,1) As TheChar,
ASCII(SUBSTRING(name,N,1)) TheAsciiCode
from my_user_table
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL) -1) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n BETWEEN 0 AND 255
AND MiniTally.n < LEN(name) +1
ORDER BY name,N
The Results:
A1A65
A20
A3H72
A40
A5S83
A60
A7G71
A80
A9u117
A100
A11e101
A120
A13s115
A140
A15t116
I think the ascii 0 value is where the UNICODE NULLS are. The above example should be 'AHSGuest', but in the table, it just looks like 'A' with nothing else after the letter A.
Here's an ugly recursion using the SQL you provided to explicitly display the names contaminated with the UNICODE NULLS:
select id, name, N as Position,
SUBSTRING(name,N,1) As TheChar,
ASCII(SUBSTRING(name,N,1)) TheAsciiCode
from my_user_table
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL) -1) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n BETWEEN 0 AND 255
AND MiniTally.n < LEN(name)+1
AND ID in
(select DISTINCT B.id FROM
(select id, name, N as Position,
SUBSTRING(name,N,1) As TheChar,
ASCII(SUBSTRING(name,N,1)) TheAsciiCode
from my_user_table
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL) -1) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n BETWEEN 0 AND 255
AND MiniTally.n < LEN(name)+1) B
WHERE B.TheAsciiCode=0)
ORDER BY name,N
Any idea on how I can get rid of these ASCII(0) characters within my strings?
Thanks for your help on this!!!
mtf
January 17, 2013 at 5:30 am
well, so far it looks like a single offending character, so i think you can do a simple replace();
if this produces the desired name , i would update it with the
sample below:
select
id,
name,
REPLACE(name,CHAR(0),'') As Fixed
from my_user_table
--only bad data for clarity
WHERE CHARINDEX(name,CHAR(0),1) > 0
If that's cleaning up the results correctly, a simple replace should fix it:
UPDATE my_user_table
SET name = REPLACE(name,CHAR(0),'')
--only bad data for to match the #rows in our original select
WHERE CHARINDEX(name,CHAR(0),1) > 0
Lowell
January 17, 2013 at 6:19 am
Just wondering what is the datalength() of one of those names, such as AHSGuest ?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 17, 2013 at 1:09 pm
Lowell,
Sadly, this isn't matching any rows:
select id, name
FROM my_user_table
WHERE CHARINDEX(name,CHAR(0),1) > 0
No rows returned. Likewise, the replace operation doesn't touch any of the rows. Ideas?
Thank you very much for the help you've provided thus far. I feel like I'm on the verge of clobbering this beast thanks to your assistance!
mtf
January 17, 2013 at 1:12 pm
Magoo,
For the 'AHSGUEST' value, here's what I see:
select id, name, DATALENGTH(name) as 'thedatalengthName'
FROM my_user_table
WHERE id='123'
123A30
mtf
January 18, 2013 at 9:45 am
mrTexasFreedom (1/17/2013)
Lowell,Sadly, this isn't matching any rows:
select id, nameFROM my_user_table
WHERE CHARINDEX(name,CHAR(0),1) > 0
No rows returned. Likewise, the replace operation doesn't touch any of the rows. Ideas?
Thank you very much for the help you've provided thus far. I feel like I'm on the verge of clobbering this beast thanks to your assistance!
mtf
What do you get if you replace the ASCII function with the UNICODE function in Lowell's original diagnostic code (the one that splits the character string into single characters and returns the character code)? If I understood correctly that your troublesome column is NVARCHAR, the UNICODE function should give you the character code to weed out, and if it's a non-printing control character, it may be different than ASCII 0.
Jason Wolfkill
January 18, 2013 at 10:54 am
or another version: a scalar function that would remove anything that was not A-Z/numeric:
same basic logic, i think:
select
id,
name,
dbo.StripNonAlphaNumeric(name) As Fixed
from my_user_table
If that's cleaning up the results correctly, a simple replace should fix it:
UPDATE my_user_table
SET name = StripNonAlphaNumeric(name)
and the function definition:
CREATE FUNCTION StripNonAlphaNumeric(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
--ascii numbers are 48(for '0') thru 57 (for '9')
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57
THEN SUBSTRING(@OriginalText,Tally.N,1)
--ascii upper case letters A-Z is 65 thru 90
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 65 AND 90
THEN SUBSTRING(@OriginalText,Tally.N,1)
--ascii lower case letters a-z is 97 thru 122
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 97 AND 122
THEN SUBSTRING(@OriginalText,Tally.N,1)
ELSE '' END
FROM tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
Lowell
January 18, 2013 at 11:06 am
mrTexasFreedom (1/17/2013)
Magoo,For the 'AHSGUEST' value, here's what I see:
select id, name, DATALENGTH(name) as 'thedatalengthName'FROM my_user_table
WHERE id='123'
123A30
mtf
I suspect that somewhere along the line, a unicode string has been badly converted, first to non-unicode with every unicode character being converted to two non-unicode characters, like you are seeing - for example, an A followed by an ascii 0, then converted back to unicode leaving you a unicode string that contains the null (zero) character in every other position.
So, it is easy to remove every other character from the strings, but the big question will be Have you lost any information in that process? if you remove the zeros and the results are OK, then fine...otherwise you may need to find the source of the data and import it correctly.
edit:
Try this conversion CONVERT(NVARCHAR(64),CONVERT(VARBINARY(128),CONVERT(CHAR(128),name)))
to see if you get something that looks vaguely right...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 18, 2013 at 3:11 pm
Magoo,
I think you're right about how there are embedded NULLS between each character. I ran the series of convert functions you provided and it showed AHSGuest††††††††††††††††††††††††††††††††††††††††††††††††††††††††††††.
I really appreciate you providing this. I might use a modified version of this in a stored procedure as I create a programmatic method of cleaning all the columns of all our tables.
I'll update this thread with that stored procedure.
mtf
January 18, 2013 at 3:18 pm
Lowell,
That was just the ticket!
The select StripNonAlphaNumeric(name) produces exactly the ASCII without the UNICODE NULLs embedded. The update also seems to work in the testing I've done so far!!!
For applying it to the entire table, I wanted to limit it to just the affected rows, so I use the earlier nested selects to narrow it down:
update my_user_table
set name=dbo.StripNonAlphaNumeric(name)
WHERE ID in
(select DISTINCT B.id FROM
(select id, name, N as Position,
SUBSTRING(name,N,1) As TheChar,
ASCII(SUBSTRING(name,N,1)) TheAsciiCode
from my_user_table
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL) -1) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n BETWEEN 0 AND 255
AND MiniTally.n < LEN(name)+1) B
WHERE B.TheAsciiCode=0)
We have these UNICODE NULLS infecting many of the columns in many of our tables. I'm going to write a stored procedure that utilizes this function to programmatically clean every column in every table. I'll update this thread with my solution soon.
For now, though, I wanted to let you know how much I appreciate the people who have helped me in this thread. I am HUGELY appreciative. Thank you so much.
mtf
January 18, 2013 at 3:22 pm
mrTexasFreedom I'm glad my code helped a bit, but mister magoo really identified the culprit, i think;
some process imported(and maybe still imports) data that should be nvarchar instead of varchar;
you should try to track down whatever that process is and fix it at the source; otherwise this cleanup-after-the-mess thing is going to be needed every time that other process runs.
Lowell
January 18, 2013 at 3:44 pm
Thank you for the endorsement on Magoo's convert trick. I am going to adjust it so the final character isn't repeated and if I can get that to work, I'll use it in my stored proc.
The source of those documents has been identified and we're putting processes in place to prevent more corrupt data from being imported. Our developers are also working on a front-end filter to dump anything that isn't standard UTF8. I'm the person tasked with cleaning up the mess that's already been created.
Have a great weekend!
mtf
January 18, 2013 at 4:34 pm
You should be able to just wrap the existing method in LEFT(....,LEN(name)) to get the correct answer...
but only if the original data was no more than half as long as your column can hold, otherwise you have lost some data...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply