Replace Azerbaijani characters in string not works correct

Question

When I want to replace Azerbaijani character 'ş' in my string with 'sh'.it works but it also replaces 's' with 'sh'

How can i solve it .Any ideas?

REPLACE(mystring,'ş','sh')

Answer 1

The character "ş" belongs to Turkish_CI_AS. It is a problem inserting them to database and retreiving them too. The trick is to use nvarchar and use N while inserting and querying.

Refer to the example below.

SELECT REPLACE(N'arshad khan earns 1000ş',N'ş','sh');

SELECT 'ş'

SELECT N'ş'

Output is as below

arshad khan earns 1000sh

s

ş

Answer 2

This character is represented as Unicode

http://en.wikipedia.org/wiki/%C5%9E

Use this article, to solve your problem

how to insert unicode text to SQL Server from query window

Regards R

Answer 3

Only solution after research.

It happens in two characters 'ş' and 'ç' if you want to replace these characters with something else it will also replace 's' and 'c'.

Of course if your database collation is 'Turkish_CI_AS' it will work .But in my case only for two characters i could not change my database collation.No logic in it.

so my client just wanted from me change Azeri characters to latin 'ş'->'s'.

My solution when i start i replace 's' with special character and put it back after i replace all azeri characters to latin.so my original 'c' and 's' characters not effected after replacement.

This is a function I wrote

Create FUNCTION [dbo].[funRGMReplaceAzeriCharacters] 
(           
    @string nvarchar (MAX)
)  
RETURNS varchar(MAX)
AS  
BEGIN 

DECLARE 
    @Result nvarchar(MAX)
    Begin 

    SET @Result=REPLACE(@string,'s' ,'V1986Q')

    SET @Result=REPLACE(@Result,'c' ,'V1987Q')

    SET @Result=REPLACE(@Result,'ı' ,'i')

    SET @Result=REPLACE(@Result,'ə','a')

    SET @Result=REPLACE(@Result,'ğ','g')

    SET @Result=REPLACE(@Result,'ü','u')

    SET @Result=REPLACE(@Result,'ş','sh')

    SET @Result=REPLACE(@Result,'ç','ch')

    Set @Result=REPLACE(@Result,'ö','o')

    -- bring back s and c

    Set @Result=REPLACE(@Result,'V1986Q','s')

    Set @Result=REPLACE(@Result,'V1987Q','c')
    END    
   RETURN UPPER (@Result)
END