How to replace unicode symbols in my strings?
Posted on 2007-07-31
I have a table wtih two nvarchar columns. (yes, they have to be nvarchars) I have an issue in a few strings with some unicode symbols appearing that do not belong there. The only symbol that I am having trouble replacing is the following:
I run a script that tells me that the ']' symbol is an nchar(9565) - if I use the replace function to try and replace the symbol with an empty string, then sql replaces it with the plus sign instead of the empty string.
Select Replace('Beads-] inch', nchar(9565),'')
If I run my script against the new string then the character code for the + sign is a char(43)
I wrapped my replace above with another replace and use char(43) and volia - I have an empty string instead of pluses.
select replace(replace('Beads-+ inch', nchar(9565),''), char(43),'')
My issue is that I need to search all of the nvarchar columns for the ']' but the above replace functions replace the '+' as well as the ']'.
Can someone explain why nchar(9565) doesn't work in the replace (is there a better function to use with the nvarchar?) Is there a way to get rid of this character without affecting legimate '+' s in my strings?