Replace arabic Letter in database

Question

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,'ى ','ي ')

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

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

Answer 1

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

Answer 2

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