Non printable characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Non printable characters

Author

Topic

Jannette
Starting Member

26 Posts

Posted - 2011-09-23 : 09:11:26

I am using the following systax to clean a notes field, however there still appears to be some non-printable characters in the field. Can you advise on how to get rid of these please ?

SUBSTRING(REPLACE(REPLACE(REPLACE(CANDIDATE.Notes, CHAR(10), ''),CHAR(13),''),',', ''),1,300) as Notes1

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-23 : 09:22:15

Try ascii 160 (hard space) too.

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-23 : 12:13:55

NOt sure if this helps, but I threw together a quick little script that you migh be able to use to see what the actual ascii value of a character is:

DECLARE @Val NVARCHAR(1000)

SET @Val = '1234' + CHAR(13) + CHAR(10) + 'abcd' + CHAR(160) + 't' + CHAR(121)

SELECT ASCII(RIGHT(LEFT(@Val, Number), 1)) ,Number
FROM master..spt_values with (nolock) 
WHERE type = 'P'
AND number <= LEN(@Val)
AND number > 0

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-25 : 02:14:10

Check this out http://iso30-sql.blogspot.com/2010/10/remove-non-printable-unicode-characters.html

Best Regards
Vadivel

http://vadivel.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources