CODEWITHSUNDEEP
sql-serversql-server-2014
Doctor Who
Doctor Who

Reputation: 792

Not able to replace special character in nvarchar string

I am trying to split a string which is nvarchar(max)
if I pass the string like 

@String_xyz = 'abc@def#ghi$jkl'

Then I am able to replace the special character but my character comes in a unreadable format like ?????

If I send my string like this way

@String_xyz = N'abc@def#ghi$jkl'

Then I am not able to replace any special character

Let say 

DECLARE @string nvarchar(max)
SET @string = 'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது'
SET @string = replace(@string,'##',' ') -- This work perfect

if

DECLARE @string nvarchar(max)
SET @string = N'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது'
SET @string = replace(@string,'##',' ') -- This will not work

Please let me know any possible solution where abc-def-ghi-jkl are multilanguage character

Collation : SQL_Latin1_General_CP1_CI_AS

Upvotes: 2

Views: 1296

Answers (2)

Uday Prasanna N
Uday Prasanna N

Reputation: 50

If your code is like

DECLARE @string nvarchar(max) = N'என்$பெயர்@PIN@ஆகிறது##என்
SET @string = replace(@string,'##',' ')

results as

என்$பெயர்@PIN@ஆகிறது என்$பெயர்@KUL@ஆகிறது

if

DECLARE @string1 nvarchar(max) = 'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது'
SET @string1 = replace(@string1,'##',' ')

then

???$?????@PIN@?????? ???$?????@KUL@??????

if

DECLARE @string2 varchar(max) = 'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது'
SET @string2 = replace(@string2,'##',' ')

then

???$?????@PIN@?????? ???$?????@KUL@??????

if

DECLARE @string3 varchar(max) = N'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது'
SET @string3 = replace(@string3,'##',' ')

then

???$?????@PIN@?????? ???$?????@KUL@??????

if you declares string as nvarchar you have to give N' before the string otherwise unicodes will not work.

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

The point is UNICODE

You have to make sure, that you use unicode in all places

Literal strings must be started with an N everywhere

Try this

--This will come out with question marks
SELECT 'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது';
--And this is the correct output
SELECT N'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது';

--Here I replace one of the characters with a "%"
SELECT REPLACE(N'என்$பெயர்@PIN@ஆகிறது##என்$பெயர்@KUL@ஆகிறது',N'கி',N'%')

This works fine here...

UPDATE

Cannot verify this anymore, but it might be, that the output was wrong at the first try. I tried around with several collations. With this I got the wanted

SELECT N'YourString' COLLATE Indic_General_90_BIN;

After having used this, it was OK. So - but this is just guessing - it might be, that SQL Server had to learn this first...

Upvotes: 1

Related Questions