How to replace unicode symbols in my strings?

Posted on 2007-07-31
Medium Priority
Last Modified: 2010-08-05
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?


Question by:Sue_W

Expert Comment

ID: 19604113
Have you tried something like Select Replace ('Beads-] inch', ']', '')??

Author Comment

ID: 19604741
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?

Accepted Solution

Computer101 earned 0 total points
ID: 22422716
PAQed with points refunded (500)

EE Admin

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question