How can we handle ascii characters using sql server 2005? Which data type can I use to store ascii characters, mainly control characters?
For example: I have a file with strings delimited by some control characters like backspace. I need to read this file into my sql server tables. I want to know what data type (char,nchar,varchar, ..) I can use to store them.
Also, all the string split functions I have seen work well with normal delimiters like ,. But my delimiter is backspace \b.
I know from this question that NVARCHAR will accommodate what you're asking for, because it deals with how to get rid of non-printable ASCII characters.
You can use my answer in that question as a basis to replace the applicable characters with something that will work better with split/substring functions. A comma isn't a great choice, because it will naturally occur in your data, but ~ is pretty safe. The ~ is the character you get when you hold the shift key and press the key to the left of the [number] one key (assuming English QWERTY/etc).
As another option you could serialize the file and store it in sql server table/ binary datatype
You could store the data as bit stream using BINARY or VARBINARY. You can actually store whatever you want in it, SQL doesn't care. If you aren't worried about performing text operations on your data, this option is pretty efficient.
Most databases will allow you to store character fields containing any character code. It's more likely a limitation of your DB access library (JDBC, ODBC, ?) that limits what you can retrieve and store from a program.
That being said, it's probably wise to convert nonprintable characters in your fields into something like C character escape sequences or URI encodings. You then convert them back into the original character codes when you read the fields from the DB.
For example, the C string "Hello,\nW\borld.\a" could be encoded in several ways:
Hello,\\nW\\borld.\\a (Note: each \\ is a single \ char)
Hello,%0AW%08orld.%07
Hello,\\u000AW\\u0008orld.\\u0007
(etc.)