How to replace unicode symbols in my strings?

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:

Beads-] inch

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),'')
'Beads-+ inch'
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),'')
'Beads- inch'

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?


Who is Participating?
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

EE Admin
Have you tried something like Select Replace ('Beads-] inch', ']', '')??
Sue_WAuthor Commented:
yep and it works but here's the thing I don't understand and the reason I wanted to use the character code  -

if I search the table column for that symbol
Select * from dbo.symboltable
where symdescription like '%]%'

I get row after row of strings with '+' signs but not the actual string with the ']' that I know needs to be replaced.  Why? How do I find the symbol?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.