CODEWITHSUNDEEP
unicodegoogle-bigquery
Sharmila Naidu AP Jayaveran -
Sharmila Naidu AP Jayaveran -

Reputation: 25

REGEX for special character (Decode Unicode)

I have try this code to decode, unicode all works fine if the comment start with any special character, symbol, space and etc shows error. 

      CREATE TEMP FUNCTION DecodeUnicode(s STRING) AS (
      IF(s NOT LIKE '%\\u%', s,
      (SELECT CODE_POINTS_TO_STRING(ARRAY_AGG(CAST(CONCAT('0x', x) AS INT64)))
      FROM UNNEST(SPLIT(s, '\\u')) AS x
      WHERE x != ''))
      );

      SELECT
      original,
      DecodeUnicode(original) AS decoded
      FROM (
      SELECT trim(r'$-\u6599\u91d1\u304c\u9ad8\u3059\u304e\uff01\uff01\uff01') AS original UNION ALL
      SELECT trim(r'abcd')
      );

Upvotes: 1

Views: 1284

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

#standardSQL
CREATE TEMP FUNCTION DecodeUnicode(s STRING) AS (
  (SELECT CODE_POINTS_TO_STRING(ARRAY_AGG(CAST(CONCAT('0x', x) AS INT64)))
  FROM UNNEST(SPLIT(s, '\\u')) AS x
  WHERE x != ''
  )
);
WITH `yourTable` AS (
  SELECT r'$-\u6599\u91d1\u304c\u9ad8\u3059\u304e\uff01\uff01\uff01' AS original UNION ALL
  SELECT r'abcd'
), uchars AS (
  SELECT DISTINCT
    c,
    DecodeUnicode(c) uchar
  FROM `yourTable`, 
  UNNEST(REGEXP_EXTRACT_ALL(original, r'(\\u[abcdef0-9]{4})')) c
)
SELECT
  original,
  STRING_AGG(IFNULL(uchar, x), '' ORDER BY pos) decoded
FROM (
  SELECT 
    original, 
    pos,
    SUBSTR(original, 
      SUM(CASE char WHEN '' THEN 1 ELSE 6 END) 
        OVER(PARTITION BY original ORDER BY pos) - CASE char WHEN '' THEN 0 ELSE 5 END, 
      CASE char WHEN '' THEN 1 ELSE 6 END) x,
    uchar
  FROM `yourTable`,
  UNNEST(REGEXP_EXTRACT_ALL(original, r'(\\u[abcdef0-9]{4})|.')) char WITH OFFSET AS pos
  LEFT JOIN uchars u ON u.c = char
)
GROUP BY original
-- ORDER BY original

What it does - it extracts all unicode characters and decode them and replace them in original string leaving non-unicodes stay as they are, so the output will be as below 

original                                                    decoded  
$-\u6599\u91d1\u304c\u9ad8\u3059\u304e\uff01\uff01\uff01     $-料金が高すぎ！！！     
abcd                                                        abcd

Upvotes: 2

Related Questions