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

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

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

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

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

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

select id, name 
FROM my_user_table
WHERE CHARINDEX(name,CHAR(0),1) > 0

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

123	A	30

select 
  id, 
  name,
dbo.StripNonAlphaNumeric(name) As Fixed
from my_user_table

UPDATE my_user_table
SET  name = StripNonAlphaNumeric(name)

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