Unicode not translating correctly for Right-to-left languages (Hebrew and Arabic)

Question

The bigquery code below provided by Mikhail Berlyant (thank you again!) works well on left-to-right languages such as Russian. However, it fails on right-to-left languages such as Arabic and Hebrew whenever there is a double quotation mark <" "> inside the text to be translated. The expected result should show all input text-to-be-translated without unicode letters inside the translation. Thanks!

CREATE TEMP FUNCTION
  decode(word string) AS ((
    SELECT
    IF
      (STARTS_WITH(word, '&#x'),
        safe.code_points_to_STRING(ARRAY(
          SELECT
            ifnull(SAFE_CAST(value AS int64),
              ASCII(value))
          FROM
            UNNEST(SPLIT(REPLACE(word, '&#', '0'),';')) value
          WHERE
            NOT value = '' )),
        word) ));
WITH
  DATA AS (
  SELECT
    'Arabic' AS lang,
    `'https://www.bbc.com/arabic/vert-fut-57352011'` AS url,
    `'هل قوام "الساعة الرملية" يكسب المرأة جاذبية أكبر؟'` AS title
  UNION ALL
  SELECT
    'Arabic',
    `'https://www.bbc.com/arabic/world-57356844'`,
    `'الكنيسة الكاثوليكية: كاردينال ألماني يقدم استقالته للبابا فرانسيس بسبب "الفشل" في التصدي للانتهاكات الجنسية بحق الأطفال'`
  UNION ALL
  SELECT
    'Arabic',
    `'https://arabic.cnn.com/world/article/2021/06/04/munich-cardinal-submits-resignation-pope'`,
    `'كاردينال ميونيخ يقدم استقالته لبابا الفاتيكان بسبب "كارثة الاعتداء الجنسي" بالكنيسة الكاثوليكية'`
  UNION ALL
  SELECT
    'Arabic',
    `'https://alghad.com/%D9%88%D8%AA%D8%AA%D8%AC%D8%AF%D8%AF-%D8%A7%D9%84%D8%A2%D9%85%D8%A7%D9%84-%D8%A8%D8%B1%D8%AD%D9%8A%D9%84-%D9%86%D8%AA%D9%86%D9%8A%D8%A7%D9%87%D9%88-%D8%A7%D9%84%D9%85%D8%B1%D8%AA%D9%82%D8%A8-%D9%84/'`,
    `'وتتجدد الآمال برحيل نتنياهو المرتقب لكن بحذر'`
  UNION ALL
  SELECT
    'Arabic',
    `'https://alghad.com/%D9%81%D9%88%D8%A8%D9%8A%D8%A7-%D8%A7%D9%84%D8%A7%D8%B1%D8%AA%D8%A8%D8%A7%D8%B7-%D8%A7%D9%86%D8%B3%D8%AD%D8%A7%D8%A8-%D9%88%D9%87%D8%B1%D9%88%D8%A8-%D9%81%D9%8A-%D8%A7%D9%84%D9%84%D8%AD%D8%B8/'`,
    `'"فوبيا الارتباط".. انسحاب وهروب في اللحظات الأخيرة'`
  UNION ALL
  SELECT
    'Hebrew',
    `'https://www.srugim.co.il/568917-%D7%9C%D7%99%D7%90%D7%95%D7%9F-%D7%91%D7%90%D7%96%D7%9B%D7%A8%D7%94-%D7%9C%D7%A8%D7%91-%D7%90%D7%9C%D7%99%D7%94%D7%95-%D7%AA%D7%95%D7%A8%D7%AA%D7%95-%D7%94%D7%99%D7%99%D7%AA%D7%94-%D7%9B%D7%95%D7%9C'`,
    `'ליאון באזכרה לרב אליהו: "תורתו הייתה כולה של י-ם"'`
  UNION ALL
  SELECT
    'Hebrew',
    `'https://celebs.walla.co.il/item/3439567'`,
    `'הכוכבת הע-נ-קית שנתפסה בצעירותה עם סמים'`
  UNION ALL
  SELECT
    'Hebrew',
    `'https://www.srugim.co.il/568906-%D7%90%D7%9C%D7%A4%D7%99-%D7%9E%D7%A4%D7%92%D7%99%D7%A0%D7%99%D7%9D-%D7%9E%D7%95%D7%9C-%D7%91%D7%99%D7%AA%D7%94-%D7%A9%D7%9C-%D7%A9%D7%A7%D7%93-%D7%90%D7%9C-%D7%AA%D7%9C%D7%9B%D7%99-%D7%A0%D7%92'`,
    `'אלפי מפגינים מול ביתה של שקד: "אל תלכי נגד ישראל"'`
  UNION ALL
  SELECT
    'Russian',
    `'https://tass.ru/kultura/11559939'`,
    `'Фестиваль "Theatrum" открывается в Новом Манеже в Москве'`),
  data2 AS (
  SELECT
    (
    SELECT
      AS STRUCT url AS url,
      lang AS lang,
      STRING_AGG(decode(word), ' ') AS translation
    FROM
      UNNEST(SPLIT(title, ' ')) word ) AS Foreign_txt
  FROM
    DATA )
SELECT
  Foreign_txt.lang AS lang,
  Foreign_txt.translation AS translation,
  Foreign_txt.url AS url
FROM
  data2;

user_5221944 · Answer

Consider below example

create temp function decode(word string) as ((
  select if(starts_with(word, '&#x'), 
    safe.code_points_to_string(array(
      select ifnull(safe_cast(value as int64), ascii(value))
      from unnest(split(replace(word, '&#', '0'),';')) value
      where not value = ''
    )), 
    word)
));
select id, lang,
  ( select string_agg(decode(chars), '' order by offset)
    from unnest(regexp_extract_all(title, r'(?:&#x.{3};)+|[^&]+')) chars with offset 
  ) as translate
from data

if applied to sample data in your question - output is

Unicode not translating correctly for Right-to-left languages (Hebrew and Arabic)

1 Answers