SQL Server Replace arabic Letter in database

ih99xse1  于 2023-08-02  发布在  其他
关注(0)|答案(3)|浏览(87)

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

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

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

5lhxktic

5lhxktic1#

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

p8h8hvxi

p8h8hvxi2#

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

6mw9ycah

6mw9ycah3#

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))

相关问题