SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to match a UNICODE NULL character within an nVarchar(128) string?


How to match a UNICODE NULL character within an nVarchar(128) string?

Author
Message
mrTexasFreedom
mrTexasFreedom
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 87
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28700 Visits: 39977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mrTexasFreedom
mrTexasFreedom
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 87
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:


A 1 A 65
A 2 0
A 3 H 72
A 4 0
A 5 S 83
A 6 0
A 7 G 71
A 8 0
A 9 u 117
A 10 0
A 11 e 101
A 12 0
A 13 s 115
A 14 0
A 15 t 116



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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28700 Visits: 39977
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4184 Visits: 7865
Just wondering what is the datalength() of one of those names, such as AHSGuest ?

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • mrTexasFreedom
    mrTexasFreedom
    SSC Journeyman
    SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

    Group: General Forum Members
    Points: 82 Visits: 87
    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
    mrTexasFreedom
    mrTexasFreedom
    SSC Journeyman
    SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

    Group: General Forum Members
    Points: 82 Visits: 87
    Magoo,

    For the 'AHSGUEST' value, here's what I see:

    select id, name, DATALENGTH(name) as 'thedatalengthName'
    FROM my_user_table
    WHERE id='123'

    123 A 30



    mtf
    wolfkillj
    wolfkillj
    UDP Broadcaster
    UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

    Group: General Forum Members
    Points: 1478 Visits: 2582
    mrTexasFreedom (1/17/2013)
    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


    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
    Blog: SQLSouth
    Twitter: @SQLSouth
    Lowell
    Lowell
    One Orange Chip
    One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

    Group: General Forum Members
    Points: 28700 Visits: 39977
    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

    --
    help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

    Group: General Forum Members
    Points: 4184 Visits: 7865
    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'

    123 A 30



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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search