asked on

How do I replace a unicode character in an ntext field on MS-SQL

My users are pasting in text from MS-Word that includes a unicode chracter: ﬀ (64256)

The field is ntext with collation set to: SQL_Latin1_General_CP1_CI_AS

I cannot even determine which rows contain the character I want to replace

Neither of these statements work:

SELECT * FROM mytable where comment LIKE NCHAR(64256)

SELECT * FROM mytable where comment LIKE N'%' + N'ﬀ' + N'%'

Open in new window

I'm hoping that once I am informed enough on how to find the rows I'm after, I can user that knowledge to write a statement to replace this character with two actual f's rather than this character.

Pawan Kumar

You have to try like this -

SELECT * FROM mytable where comment LIKE N'%' AND comment LIKE N'ﬀ' AND comment LIKE N'%'

or this

SELECT * FROM mytable where comment LIKE N'%' OR comment LIKE N'ﬀ' OR comment LIKE N'%'

qinformation

ASKER

Pawan: Neither of those statements seem to return the right result set.

Pawan Kumar

Sorry what are you searching ?

Pawan Kumar

If you are searching string starts with ff then we have to use below -

SELECT * FROM mytable where comment LIKE N'ﬀ%'

ste5an

This should work:

SELECT * 
FROM mytable 
WHERE comment LIKE N'%' + NCHAR(64256) + N'%';

Open in new window

qinformation

ASKER

The result here returns both rows but should only return the one with the unicode character:

CREATE table #temp (testcolumn ntext)
insert INTO #temp SELECT N'ﬀ'
insert INTO #temp SELECT 'ff'

SELECT * FROM #temp where testcolumn LIKE '%' + N'ﬀ' + '%'

Open in new window

qinformation

ASKER

Thanks ste5an. Your suggestion returns double f's. See my #temp table example for reference

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

qinformation

ASKER

Many Thanks!

qinformation

ASKER

appreciate the help all!