I have a few characters that are being replaced by a ?. How do you keep the original character in the table? The character that I am trying to insert is a ɬ; Latin Small Letter L With Belt. Is it possible to add this character into the table?

  • Please do not speak about "special" characters, even if everyone does this abuse. No character is special, especially outside of any context. They are all streams of bits for our computers. – Patrick Mevzek May 31 at 22:54
up vote 4 down vote accepted

It is possible to add that character as long as either:

  1. the destination column is NCHAR / NVARCHAR / XML (NTEXT also works, but has been deprecated since the release of SQL Server 2005 so don't use it),

    OR

  2. the destination column is CHAR / VARCHAR and the Collation of the column uses a Code Page that supports the character(s) in question.

So, make sure to do the following:

  1. Prefix the string with N (not required if the current Database's default Collation uses a Code Page that supports the character, but helps in that it removes the Database's default Collation from being a factor that could cause this issue -- reason being that if the current DB's default Collation uses a Code Page that doesn't support the character, then it will be converted to either a "best fit" match, or ? if no best fit match can be found, while it is being held in a variable or string literal).
  2. Along those same lines, if this is coming into a stored procedure or parameterized query batch via a parameter and the parameter is of type CHAR or VARCHAR, then the current Database's default Collation does not use a Code Page that supports the character, so switch to using NVARCHAR / NCHAR as the parameter datatype.
  3. If the destination column is VARCHAR / CHAR, then be sure that the Collation of the column uses a Code Page that supports that character.
  4. If the destination column is VARCHAR / CHAR and you cannot find a Collation that uses a Code Page that supports the character(s) in question, then you need to convert the column to NVARCHAR / NCHAR so that it can store any character.   ( This is what worked for the O.P. )

For more info on working with Collations, please visit: Collations.info

  • Thank you. Changing the column to be NVARCHAR allowed me to store the string with the special character – kevorski May 31 at 18:37
  • @kevorski You are welcome. Glad to help, and thanks for mentioning which option worked :-) – Solomon Rutzky May 31 at 18:41
  • 1
    @kevorski Again, thanks. And thank you for going back and accepting a few more of your previous questions (it really does help the site). I just added a link at the bottom of my answer to a resource for more info on this topic. Finally, we can go ahead and clean up these comments. I have deleted some of mine already. If you delete yours, I will go ahead and remove all of mine, including this one. Take care... – Solomon Rutzky Jun 1 at 17:04

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.