CODEWITHSUNDEEP
sqlsql-server
varda
varda

Reputation: 11

Reverse characters in string with mixed Left-to-right and Right-to-left languages using SQL 2?

I have string values in my table which includes Hebrew characters (or any R-T-L language for this case) and English ones (or numbers).

The problem is that the English characters are reversed and looks like: בדיקה 123456 esrever sti fI kcehC.

The numbers and the English characters are reversed, the Hebrew ones are fine.

How can I use built in SQL functions to identify the English substring (and the numbers) and reverse it while maintain the order on the other RTL characters?

I read an answer to this problem in the following link: Reverse characters in string with mixed Left-to-right and Right-to-left languages using SQL?

The solution:

DECLARE @sourceString NVARCHAR(100) = N'123456 בדיקה esrever sti fI kcehC';
DECLARE @reversedString NVARCHAR(4000)  = nchar(8237) + REVERSE(@sourceString) +  nchar(8236)
SELECT @reversedString;

My problem: When I try to build queries with this option it does not work For example:

select count(*) from table where nchar(8237) + REVERSE(column) +  nchar(8236) = N'‭Check If its reverse הקידב 654321‬'

How can I fix it?

Upvotes: 1

Views: 1406

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The behavior of RTL strings in SQL-Server, especially when mixed, is sometimes really odd.

I don't know exactly what you are trying to achieve, but this would - at least - give you the unicode code points:

DECLARE @source NVARCHAR(100)=N'123456 בדיקה esrever sti fI kcehC';

WITH cte AS
(
    SELECT 1 AS pos
          ,UNICODE(SUBSTRING(@source,1,1)) AS CodeAtPosition
    UNION ALL
    SELECT cte.pos+1
          ,UNICODE(SUBSTRING(@source,cte.pos+1,1))
    FROM cte
    WHERE pos<LEN(@source)
)
SELECT * 
FROM cte

The hebrew characters are from a much higher range:

pos CodeAtPosition

1   49
2   50
3   51
4   52
5   53
6   54
7   32
8   1489 <-- starting here
9   1491
10  1497
11  1511
12  1492 <-- ending here
13  32
14  101
...more rows

Helpful answer: https://stackoverflow.com/a/29821207/5089204
Something about bidi-text: https://www.w3.org/TR/WCAG20-TECHS/H34.html

Upvotes: 1

Related Questions