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

—There is a handy function with nested REPLACEs to remove all no printable characters:


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s