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