Special Characters in an Int column from Text File


Char Dec Hex Octal HTML Function / Description / Notes

^I 9 0x09 0011 ^I HT horizonal tab [\t]
^J 10 0x0a 0012 ^J LF line feed [\n]
^M 13 0x0d 0015 ^M CR carriage return [\r]
^Z 26 0x1a 0032 ^Z SUB substitute

SELECT Cast(Replace(Replace(Replace(Replace(ANumber,char(9),”),char(10),”) ,char(13),”) ,char(26),”) as INT) ANumber

http://www.idautomation.com/product-support/ascii-chart-char-set.html
—There is a handy function with nested REPLACEs to remove all no printable characters:
http://www.sqlservercentral.com/Forums/Topic860321-338-3.aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s