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

Question

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? Any workaround will do :-) ... thanks

Answer 1

I believe that your entire string is reversed and the fact that the Hebrew words are displaying in the correct order is actually the result of a different problem. What I suspect is that the Hebrew words are stored in a non-lexical order.

In theory you should be able to resolve your problem by simply reversing the string and then force SQL Server to display the Arabic words from left to right. This is done by appending a special character to the front and back of your string as follow:

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

    DECLARE @reversedString NVARCHAR(4000)  = nchar(8237) + REVERSE(@sourceString) +  nchar(8236)

    SELECT @reversedString;