How to replace unicode symbols in my strings?

Posted on 2007-07-31
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
    LVL 8

    Expert Comment

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

    Author Comment

    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?
    LVL 1

    Accepted Solution

    PAQed with points refunded (500)

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now