Reverse characters in string with mixed Left-to-ri

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;

Answer 2

You can use ASCII function in SQL Server for getting the ascii value of characters in the text field in DB. Once you get the ascii value, compare that against the valid range of english visible characters and numerals. Anything else can be considered as Hebrew character.

Also there exists REVERSE function automatically in SQL Server for reversing the string as required.

Following link has some sample code.

http://www.sqlservercurry.com/2009/09/how-to-find-ascii-value-of-each.html

Answer 3

I've never worked with Hebrew characters so I'm not sure if this will work,

However I think you can implement a function with a while loop using patindex

• you'll need a variable for holding the reversed english part @EngTemp
• a variable to hold the substring currently being processed @SubTemp
• a variable to hold the remaining text in the string that still needs to be processed @SubNext
• a variable to hold the length of the current substring @Len
• an output variable @Out

Steps:

• Take a string input, put it into @SubNext while PatIndex('%[^a-z]%', @SubNext) > 0
• substring to the pat index store in @SubTemp, also trim @SubNext to the patindex
• store the length of the @SubTemp in @Len
• if @Len > 1; set @Out = @Out + @EngTemp + @SubTemp; Set @EngTemp = '' (This step assumes the possibility that there could be cases where the english string is not the end of the line)
• if @Len = 1; set @EngTemp = @SubTemp + @EngTemp
• if @Len = 0; set @Out = @Out + @EngTemp (At this point the loop should close also)

I'm going to play with this when I have some time and post actual code, sorry if my scribbles doesn't make any sense