Replace arabic Letter in database

user_11867031 0
Viewed 1086

When changing the letter (ي) at the word to the letter (ى) changes and there is no problem, but the problem in changing the letter (ي) in the middles of the letter?

Is there a solution to ignore the middles of the letter?

DECLARE @FullName        VARCHAR(100)
SET @FullName = 'عبدالله عيد محمد علي'

Select @FullName, REPLACE(@FullName,'ى ','ي ')

-- عبدالله عيد محمد علي

-- عبدالله عىد محمد على

arabic sql-server replace database
3 Answers

Try using their Unicode equivalents

Select NCHAR(1740) as N'ي فارسي - Persian Ye', 
       NCHAR(1610) as N'ي عربي - Arabic Ye',
       NCHAR(1705) as N'ك فارسي - Persian Ke',
       NCHAR(1603) as N'ك عربي - Arabic Ke'

Such as

DECLARE @FullName  NVARCHAR(100)
SET @FullName = N'عبدالله عيد محمد علي'
Select @FullName, REPLACE(@FullName, NCHAR(1610), NCHAR(1740))

With this output enter image description here

user_2985730

You can use a combination of STRING_SPLIT ,STUFF and REVERSE to split the string by spaces and then replace the last occurrence of a character in each word. And then you can use STRING_AGG to concatenate back with spaces.

DECLARE @FullName NVARCHAR(100)
SET @FullName = 'testexecution testexecution'

SELECT STRING_AGG(VALUE,' ') AS UpdatedFullName FROM
( 
SELECT STUFF(VALUE, LEN(VALUE) +1 - CHARINDEX('t', REVERSE(VALUE)), 1, 'k') AS VALUE   -- Replace last occurence of 't' with 'k'
from STRING_SPLIT(@FullName,' ')
) AS ReplacedResult

NOTE: This will require SQL Server 2017 or higher to work

Also your database seems to use multiple languages so I'd suggest to use NVARCHAR instead of VARCHAR to support Unicode

user_77133720

You can use the following query, by replacing [Table_name] and [Field_name] with your variables:

update [Table_name] 
set [Field_name] = REPLACE(REPLACE(CAST([Field_name] as nvarchar(max)) ,  NCHAR(1610), NCHAR(1740)),NCHAR(1603) , NCHAR(1705))
user_45712360
Related