MaxCompute：逸出字元

SQL（MaxCompute）

在SQL文法中，字串的內容需要寫在半形單引號（''）或者雙引號（""）中，例如"abc"、'123'。對於一些字串中本身就有單引號或者雙引號的情況，如果只包括一種引號，則可以簡單使用另一種引號，例如 '雙引號(")'。但是如果一段文字中既包括半形單引號又包括半形雙引號，例如如下語句：

雙引號(")
單引號(')

不僅包含了''和""，還包含了分行符號，對於這種，規定使用平時較少用到的反斜線（\）作為特殊字元，表示和後面的字元共同表示一個字元，例如\n表示換行，\"、\'表示字串中的引號，並不代表字串結束，則上面的內容可以寫成一個字串：'雙引號(")\n單引號(\')' 或者 "雙引號(\")\n單引號(')"。

但是這樣反斜線又有了特殊的含義，對於字串中的反斜線，就要寫成\\，第一個表示轉義，第二個表示真正的字元。SQL中常見的逸出字元寫法如下：

Regex

Regex除了對一般的不可見字元使用\，為了做一些文本匹配，聲明了模式，而這些模式中大量使用了()^%等符號，例如MaxCompute的底層正則引擎使用了RE2，RE2詳情請參見RE2，一些使用\的轉義寫法如下：

完整的正則文法非常複雜，您可以使用regexr和regex101線上工具寫出符合預期的Regex。

JSON

JSON是一種常用的傳輸資料的文本協議，標準比較簡單，完整的標準請參見JSON，使用\的轉義寫法如下：

可以看到JSON許多逸出字元的設計和SQL非常相似，您可以使用JSON線上工具JSONLint驗證一段文本是否符合JSON規範。

逸出字元後字元解釋為其本身

• MaxCompute SQL中的字串常量可以用單引號或雙引號表示。您可以在單引號括起的字串中包含雙引號，或在雙引號括起的字串中包含單引號，否則要用逸出字元來表達。表達方式如下：

  "I'm a happy manong."
  'I\'m a happy manong.'

• 在LIKE字元匹配中，要匹配%或_本身，則要對其進行轉義：

  select 'ab_cde' like 'ab\_c%';
  
  --返回結果
  true

特殊字元

• 'a\tb'字串裡有三個字元，\t被視為一個字元。

  select length('a\tb');
  
  --返回結果
  3

• 'a\ab'字串裡有三個字元，\a被解釋為普通的a。

  select 'a\ab',length('a\ab');
  
  --返回結果
  aab,3

JSON+SQL轉義

有一段JSON：{"key":"this is very \"important\"."}，想要使用get_json_object函數提取Value值，卻得不到正確的結果：

-- 使用新版本的get_json_object，會檢查json的完整性
set odps.sql.udf.getjsonobj.new=true;
select get_json_object('{"key":"this is very \"important\"."}', '$.key');

--返回結果
NULL

原因出現在這段JSON文本只是簡單地在兩側加了半形單引號，裡面的內容並沒有經過SQL的轉義，直接select文本驗證此結論：

select '{"key":"this is very \"important\"."}'; 

--返回結果
{"key":"this is very "important"."}

可以看到反斜線消失了，原因是編譯器解析的時候把JSON中的逸出字元當做了SQL的逸出字元，\" 被解釋成了 "，導致得到的結果並不符合JSON文法，從而無法解析。對JSON文本加上正確的SQL逸出字元：'{"key":"this is very \\"important\\"."}'，再次使用get_json_object函數提取Value值，得到正確的結果：

set odps.sql.udf.getjsonobj.new=true;
select get_json_object('{"key":"this is very \\"important\\"."}', '$.key');

--返回結果
this is very "important".

簡單文本+Regex+SQL轉義

有一段文字：010-12345678，使用函數提取出最前面的010步驟如下：

1. 寫出Regex(\d+)-。

2. 根據SQL的轉義規則，對其中的\進行轉義，同時在兩邊添加單引號，可以得到字串'(\\d+)-'。

在MaxCompute中執行如下命令進行驗證：

select REGEXP_EXTRACT('010-12345678', '(\\d+)-');

--返回結果
010

JSON+Regex+SQL轉義

如果字串本身就含有逸出字元，例如{"key":"this is very \"important\"."}這段JSON，使用Regex匹配出其中的important步驟如下：

1. 寫出Regex：\"(.*)\"。

2. 對其中的反斜線進行Regex的轉義：\\"(.*)\\"。

3. 再對錶達式進行SQL的轉義，將所有的\替換成\\，為了簡便，兩邊再添加單引號，得到字串：'\\\\"(.*)\\\\"'。

在MaxCompute中執行如下命令進行驗證：

select REGEXP_EXTRACT('{"key":"this is very \\"important\\"."}', '\\\\"(.*)\\\\"');

--返回結果
important

結果符合預期，類似此情境建議先對JSON進行解析，邏輯會更清晰，命令如下：

set odps.sql.udf.getjsonobj.new=true;
select REGEXP_EXTRACT(get_json_object('{"key":"this is very \\"important\\"."}', '$.key'), '"(.*)"');

--返回結果
important