3

I've an nvarchar column with English and Hebrew letters in some fields.

In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program inserts them into the fields) when I view them outside the ERP program.

How can I reverse only the English words where the fields have both English and Hebrew in them?

Examples:


שורת EDULCNI - הפעלת עמודה

needs to be

שורת INCLUDE - הפעלת עמודה
מסך MOOZ דינמי

needs to be

מסך ZOOM דינמי
סימון מצררים/PRM

needs to be

סימון מצררים/MRP
4
  • 2
    Background for those wondering why. SQL Server supports complex language display. I've added the collation tag - perhaps that will help your question get noticed by someone who can help.
    – Hannah Vernon
    Oct 26, 2021 at 12:33
  • 2
    Perhaps this helps?
    – Hannah Vernon
    Oct 26, 2021 at 12:47
  • 1
    Hello Avi. There are a couple of possibilities here, but first we need to know how the characters are actually stored (how they are displayed can sometimes be misleading). So, please execute the following for at least one of the lines mentioned in the question and copy the result into the question, between the line showing what you see and the "needs to be": SELECT CONVERT(VARBINARY(MAX), [{column_name}]) FROM .... Oct 26, 2021 at 13:39
  • 2
    Avi, two more questions: 1) Do you have rows of this same field that are either entirely English or entirely Hebrew? If so, please provide an example, both in terms of what you see as well as the output from the CONVERT function I noted in my previous comment. 2) Is there ever any punctuation within the English words, such as commas, periods, apostrophes, etc? (example: Mrs. O'Leary) Or, is there ever multiple English words grouped together? (example: INCLUDE ZOOM) If so, please provide an example (both what you see and the VARBINARY output. Thanks. Oct 26, 2021 at 14:49

1 Answer 1

-2

As commented by Solomon Rutzky, the code I've made before didn't work with english phrases, so I've refactored it, and now it works on all the examples:

create or alter function reverse_english_only(@str nvarchar(max))
returns nvarchar(max) AS
begin
    declare @output nvarchar(max) = N''
    declare @size int = DATALENGTH(@str)
    declare @iterate int = 1
    declare @block_start int = 1, @block_type char(1), @current NCHAR(1)
    set @block_type = case when UNICODE(@str)>=1424 and UNICODE(@str)<=1524 then 'H' else 'E' end
    while @iterate < @size
    BEGIN
        set @current = SUBSTRING(@str, @iterate,1)
        --Hebrew
        if UNICODE(@current) >=1424 and UNICODE(@current)<=1524
        BEGIN
            if @block_type='E'
            begin
                set @output += REVERSE(SUBSTRING(@str, @block_start, @iterate-@block_start))
                set @block_type = 'H'
                set @block_start = @iterate
            end
        END
        --Non-Hebrew
        ELSE
        BEGIN
            if @block_type = 'H'
            begin
                set @output += SUBSTRING(@str, @block_start, @iterate-@block_start)
                set @block_type='E'
                set @block_start=@iterate
            end
        end
        set @iterate = @iterate + 1
    END
    if @block_type = 'H'
        set @output += SUBSTRING(@str, @block_start, @iterate-@block_start)
    else
        set @output += REVERSE(SUBSTRING(@str, @block_start, @iterate-@block_start))
    return @output
END
GO
select
dbo.reverse_english_only(N'שורת EDULCNI - הפעלת עמודה'),
dbo.reverse_english_only(N'מסך MOOZ דינמי'),
dbo.reverse_english_only(N'סימון מצררים/PRM'),
dbo.reverse_english_only(N'yraeL''O .srM - הפעלת עמודה'),
dbo.reverse_english_only(N'שורת OWT ENO - הפעלת עמודה')
1
  • 3
    Hello Jorge. While this might work, it heavily depends on the source data only ever being a single word and not multiple non-Hebrew words, separated by spaces. For example, this solution does not handle the following correctly: SELECT dbo.reverse_english_only(N'שורת yraeL''O .srM - הפעלת עמודה',' '); SELECT dbo.reverse_english_only(N'שורת OWT ENO - הפעלת עמודה',' ');. Also, depending on what is actually in the source data (we won't know until the O.P. answers my questions), this could end up making thing worse. Ideally, it's best to not physically reorder Unicode data. Oct 26, 2021 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.