內建函數-上__SQL_大數據計算服務-阿裏雲

ABS

函數定義：

    double abs(double number)
    bigint abs(bigint number)

用途：返回絕對值。

參數說明：

number：Double或bigint類型，輸入為bigint時返回bigint，輸入為double時返回double類型。若輸入為string類型會隱式轉換到double類型後參與運算，其它類型拋異常。

返回值：Double或者bigint類型，取決於輸入參數的類型。若輸入為null，返回null。

備注：

當輸入bigint類型的值超過bigint的最大表示範圍時，會返回double類型，這種情況下可能會損失精度。

示例：

    abs(null) =  null
    abs(-1) = 1
    abs(-1.2) = 1.2
    abs("-2") = 2.0
    abs(122320837456298376592387456923748) = 1.2232083745629837e32

下麵是一個完整的abs函數在SQL中使用的例子，其他內建函數(除窗口函數、聚合函數外)的使用方式與其類似，不再一一舉例：

    select abs(id) from tbl1;
    -- 取tbl1表內id字段的絕對值

ACOS

函數定義：

    double acos(double number)

用途：計算number的反餘弦函數。

參數說明：

number：Double類型，-1≤number≤1。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。

返回值：Double類型，值域在0 ~ π 之間。若number為NULL，返回NULL。

示例：

    acos("0.87") = 0.5155940062460905
    acos(0) = 1.5707963267948966

ASIN

函數定義：

    double asin(double number)

用途：反正弦函數。

參數說明：

number：Double類型，-1≤number≤1。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。

返回值：Double類型，值域在-π/2 ~π/2之間。若number為NULL，返回NULL。

示例：

    asin(1) = 1.5707963267948966
    asin(-1) = -1.5707963267948966

ATAN

函數定義：

    double atan(double number)

用途：反正切函數。

參數說明：

number：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。

返回值：Double類型，值域在-π/2 ~π/2之間。若number為NULL，返回NULL。

示例：

    atan(1) = 0.7853981633974483
    atan(-1) = -0.7853981633974483

CEIL

函數定義：

    bigint ceil(double value)

用途：返回不小於輸入值value的最小整數

參數說明：

value：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。

返回值：Bigint類型。任一輸入為NULL，返回NULL。

示例：

    ceil(1.1) = 2
    ceil(-1.1) = -1

CONV

函數定義：

    string conv(string input, bigint from_base, bigint to_base)

用途：進製轉換函數

參數說明：

input：以string表示的要轉換的整數值，接受bigint，double的隱式轉換。
from_base，to_base：以十進製表示的進製的值，可接受的的值為2，8，10，16。接受string及double的隱式轉換。

返回值：String類型。任一輸入為NULL，返回NULL。轉換過程以64位精度工作，溢出時報異常。輸入如果是負值，即以”-“開頭，報異常。如果輸入的是小數，則會轉為整數值後進行進製轉換，小數部分會被舍棄。

示例

    conv('1100', 2, 10) = '12'
    conv('1100', 2, 16) = 'c'
    conv('ab', 16, 10) = '171'
    conv('ab', 16, 16) = 'ab'

COS

函數定義：

    double cos(double number)

用途：餘弦函數，輸入為弧度值。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。

返回值：Double類型。若number為NULL，返回NULL。

示例：

    cos(3.1415926/2)=2.6794896585028633e-8
    cos(3.1415926)=0.9999999999999986

COSH

函數定義：

    double cosh(double number)

用途：雙曲餘弦函數。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若number為NULL，返回NULL。

COT

函數定義：

    double cot(double number)

用途：餘切函數，輸入為弧度值。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若number為NULL，返回NULL。

EXP

函數定義：

    double exp(double number)

用途：指數函數。返回number的指數值。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若number為NULL，返回NULL。

FLOOR

函數定義：

    bigint floor(double number)

用途：向下取整，返回比number小的整數值。

參數說明：

number：Double類型，若輸入為string類型或bigint型會隱式轉換到double類型後參與運算，其他類型拋異常返回值：返回Bigint類型。若number為NULL，返回NULL。

示例

    floor(1.2)=1
    floor(1.9)=1
    floor(0.1)=0
    floor(-1.2)=-2
    floor(-0.1)=-1
    floor(0.0)=0
    floor(-0.0)=0

LN

函數定義：

    double ln(double number)

用途：返回number的自然對數。

參數說明：

number：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。若number為NULL返回NULL,若number為負數或零，則拋異常。返回值：Double類型。

LOG

函數定義：

    double log(double base, double x)

用途：返回以base為底的x的對數。

參數說明：

base：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。
x：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型的對數值，若base和x中存在NULL，則返回NULL；若base和x中某一個值為負數或0，會引發異常；若base為1(會引發一個除零行為)也會引發異常。

POW

函數定義：

    double pow(double x, double y)

用途：返回x的y次方，即x^y。

參數說明：

X：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。
Y：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若x或y為NULL，則返回NULL

RAND

函數定義：

    double rand(bigint seed)

用途：以seed為種子返回double類型或Decimal類型的隨機數，返回值區間是的0～1。

參數說明：

seed：可選參數，Bigint類型，隨機數種子，決定隨機數序列的起始值。

返回值：Double類型或Decimal類型。

示例：

select rand() from dual;
select rand(1) from dual;

ROUND

函數定義：

    double round(double number, [bigint decimal_places])

用途：四舍五入到指定小數點位置。

參數說明：

number：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。
decimal_place：Bigint類型常量，四舍五入計算到小數點後的位置，其他類型參數會引發異常. 如果省略表示四舍五入到個位數。默認值為0。返回值：返回Double類型。若number或decimal_places為NULL，返回NULL。

備注：

decimal_places可以是負數。負數會從小數點向左開始計數，並且不保留小數部分；如果decimal_places超過了整數部分長度，返回0.示例：

    round(125.315) = 125.0
    round(125.315, 0) = 125.0
    round(125.315, 1) = 125.3
    round(125.315, 2) = 125.32
    round(125.315, 3) = 125.315
    round(-125.315, 2) = -125.32
    round(123.345, -2) = 100.0
    round(null) = null
    round(123.345, 4) = 123.345
    round(123.345, -4) = 0.0

SIGN

函數聲明：

 sign(x)

用途：判斷x是否為正值或者是否為負值。參數說明：x:Double類型或者Decimal類型，可以為常量、函數或者表達式。返回值：

當x為正值時，返回1.0。
當x為負值時，返回-1.0。
當x為0時，返回0.0。
當x為空時，拋異常。

示例：

select sign(5-13) from dual;
返回：  
+------------+
| _c0        |
+------------+
| -1.0       |
+------------+

SIN

函數定義：

    double sin(double number)

用途：正弦函數，輸入為弧度值。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若number為NULL，返回NULL。

SINH

函數定義：

    double sinh(double number)

用途：雙曲正弦函數。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若number為NULL，返回NULL。

SQRT

函數定義：

    double sqrt(double number)

用途：計算平方根。

參數說明：

number：Double類型，必須大於0。小於0時引發異常。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：返回double類型。若number為NULL，返回NULL。

TAN

函數聲明：

    double tan(double number)

用途：正切函數，輸入為弧度值。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若number為NULL，返回NULL。

TANH

函數聲明：

    double tanh(double number)

用途：雙曲正切函數。

參數說明：

number：Double類型。若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。返回值：Double類型。若number為NULL，返回NULL。

TRUNC

函數聲明：

    double trunc(double number[, bigint decimal_places])

用途：將輸入值number截取到指定小數點位置。

參數說明：

number：Double類型，若輸入為string類型或bigint類型會隱式轉換到double類型後參與運算，其他類型拋異常。
decimal_places：Bigint類型常量，要截取到的小數點位置，其他類型參數會隱式轉為bigint，省略此參數時默認到截取到個位數。返回值：返回值類型為Double。若number或decimal_places為NULL，返回NULL。

備注:

截取掉的部分補0。
decimal_places可以是負數，負數會從小數點向左開始截取，並且不保留小數部分；如果decimal_places超過了整數部分長度，返回0。

示例：

    trunc(125.815) = 125.0
    trunc(125.815, 0) =125.0
    trunc(125.815, 1) = 125.8
    trunc(125.815, 2) = 125.81
    trunc(125.815, 3) = 125.815
    trunc(-125.815, 2) = -125.81
    trunc(125.815, -1) = 120.0
    trunc(125.815, -2) = 100.0
    trunc(125.815, -3) = 0.0
    trunc(123.345, 4) = 123.345
    trunc(123.345, -4) = 0.0

字符串函數

CHAR_MATCHCOUNT

函數聲明：

    bigint char_matchcount(string str1, string str2)

用途：用於計算str1中有多少個字符出現在str2中。

參數說明：

str1，str2：String類型，必須為有效的UTF-8字符串，如果對比中發現有無效字符則函數返回負值。

返回值：Bigint類型。任一輸入為NULL返回NULL。

示例：

    char_matchcount('abd', 'aabc') = 2
    -- str1中得兩個字符串'a', 'b'在str2中出現過

CHR

函數聲明：

    string chr(bigint ascii)

用途：將給定ASCII碼ascii轉換成字符。

參數說明：

ascii：Bigint類型ASCII值，若輸入為string類型或double類型會隱式轉換到bigint類型後參與運算，其它類型拋異常。

返回值：String類型。參數範圍是0~255，超過此範圍會引發異常。輸入值為NULL返回NULL。

CONCAT

函數聲明：

    string concat(string a, string b...)

用途：返回值是將參數中的所有字符串連接在一起的結果。

參數說明：

a，b等為String類型，若輸入為bigint，double或datetime類型會隱式轉換為string後參與運算，其它類型報異常。

返回值：String類型。如果沒有參數或者某個參數為NULL，結果均返回NULL。

示例：

    concat('ab', 'c') = 'abc'
    concat() = NULL
    concat('a', null, 'b') = NULL

GET_JSON_OBJECT

函數聲明:

    STRING GET_JSON_OBJECT(STRING json,STRING path)

用途：在一個標準json字符串中，按照path抽取指定的字符串.

參數說明：

json:String類型，標準的json格式字符串。
path: String類型，用於描述在json中的path，以$開頭。 關於新實現中json path的說明：參考https://goessner.net/articles/JsonPath/index.html#e2 $表示根節點 “.”表示child “[number]”表示數組下標對於數組，格式為 key[sub1][sub2][sub3]…… []返回整個數組* 不支持轉義

返回值：String類型

注解：

如果json為空或者非法的json格式，返回NULL
如果path為空或者不合法（json中不存在）返回NULL
如果json合法，path也存在則返回對應字符串

示例1

+----+
 json
+----+
{"store":
   {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
      "bicycle":{"price":19.95,"color":"red"}
   },
   "email":"amy@only_for_json_udf_test.net",
   "owner":"amy"
 }

通過以下查詢，可以提取json對象中的信息：

 odps> SELECT get_json_object(src_json.json, '$.owner') FROM src_json;
 amy
 odps> SELECT get_json_object(src_json.json, '$.store.fruit[0]') FROM src_json;
 {"weight":8,"type":"apple"}
 odps> SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json;
 NULL

示例2

get_json_object('{"array":[[aaaa,1111],[bbbb,2222],[cccc,3333]]}','$.array[1].[1]') = "2222"
get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]') = "["h0","h1","h2"]"
get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]') = "h1"

`INSTR`

函數聲明：


    bigint instr(string str1, string str2[, bigint start_position[, bigint nth_appearance]])
用途：計算一個子串str2在字符串str1中的位置。
參數說明：
str1：String類型，搜索的字符串，若輸入為bigint，decimal，double或datetime類型會隱式轉換為string後參與運算，其它類型報異常。
str2：String類型，要搜索的子串，若輸入為bigint，decimal，double或datetime類型會隱式轉換為string後參與運算，其它類型報異常。
start_position：Bigint類型，其它類型會拋異常，表示從str1的第幾個字符開始搜索，默認起始位置是第一個字符位置1。當start_position為負數時表示開始位置是從字符串的結尾往前倒數，最後一個字符是-1，往前數依次就是-2，-3…。
nth_appearance：Bigint類型，大於0，表示子串在字符串中的第nth_appearance次匹配的位置，如果nth_appearance為其它類型或小於等於0會拋異常。
返回值：Bigint類型。

備注:
如果在str1中未找到str2，返回0。
任一輸入參數為NULL返回NULL
如果str2為空串時總是能匹配成功，因此instr(‘abc’, ‘’) 會返回1。
示例：
    instr('Tech on the net', 'e') = 2
    instr('Tech on the net', 'e', 1, 1) = 2
    instr('Tech on the net', 'e', 1, 2) = 11
    instr('Tech on the net', 'e', 1, 3) = 14
    instr('Tech on the net', 'e', -1, 1) = 14
    instr('Tech on the net', 'e', -3, 2) = 2
    instr('Tech on the net', 'o', -1, 2) = 0
IS_ENCODING
函數聲明：
    boolean is_encoding(string str, string from_encoding, string to_encoding)
用途：判斷輸入字符串str是否可以從指定的一個字符集from_encoding轉為另一個字符集to_encoding。可用於判斷輸入是否為”亂碼”，通常的用法是將from_encoding設為”utf-8”，to_encoding設為”gbk”。
參數說明：
str：String類型，輸入為NULL返回NULL。空字符串則可以被認為屬於任何字符集。
from_encoding，to_encoding：String類型，源及目標字符集。輸入為NULL返回NULL。
返回值：Boolean類型，如果str能夠成功轉換，則返回true，否則返回false
示例：
    is_encoding('測試', 'utf-8', 'gbk') = true
    is_encoding('測試', 'utf-8', 'gbk') = true
    -- gbk字庫中有這兩個繁體字
    is_encoding('測試', 'utf-8', 'gb2312') = false
    -- gb2312庫中不包括這兩個字
KEYVALUE
函數聲明:
KEYVALUE(STRING srcStr，STRING split1，STRING split2， STRING key)
KEYVALUE(STRING srcStr， STRING key) //split1 = ";"，split2 = ":"
用途: 將srcStr（源字符串）按split1分成“key-value”對，按split2將key-value對分開，返回“key”所對應的value。
參數說明：
srcStr 輸入待拆分的字符串。
key：string類型。源字符串按照split1和split2拆分後，根據該key值的指定，返回其對應的value。
split1, split2：用來作為分隔符的字符串，按照指定的這兩個分隔符拆分源字符串。如果表達式中沒有指定這兩項，默認split1為’;’, split2為’:’。當某個被split1拆分後的字符串中有多個split2時，返回結果未定義；
返回值: 
String類型。
Split1或split2為NULL時，返回NULL。
srcStr,key為NULL或者沒有匹配的key時，返回NULL。
如果有多個key-value匹配，返回第一個匹配上的key對應的value。
示例：
keyvalue('0:1;1:2', 1) = '2'
 -源字符串為“0:1;1:2”，因為沒有指定split1和split2，默認split1為";"，split2為“:”。經過split1拆分後，key-value對為：
 0:1,1:2  
 經過split2拆分後變成：  
 0 1/  
 1 2  
 返回key為1所對應的value值，為2。
keyvalue(";decreaseStore:1;xcard:1;isB2C:1;tf:21910;cart:1;shipping:2;pf:0;market:shoes;instPayAmount:0;", ";",":","tf") = "21910"  
  -源字符串為“;decreaseStore:1;xcard:1;isB2C:1;tf:21910;cart:1;shipping:2;pf:0;market:shoes;instPayAmount:0;”，按照 split1“;”拆分後，得出的key-value對為：  
 decreaseStore:1，xcard:1，isB2C:1，tf:21910，cart:1，shipping:2，pf:0，market:shoes，instPayAmount:0  
 按照split2":"拆分後變成：  
 decreaseStore 1  
 xcard 1  
 isB2C 1  
 tf 21910  
 cart 1  
 shipping 2  
 pf 0  
 market shoes  
 instPayAmount 0  
 key值為“tf”,返回其對應的value:21910。  
keyvalue("阿裏雲=飛天=2;飛天=數據平台", ";","=", "阿裏雲") 返回NULL，請用戶避免這種用法。

LENGTH
命令格式：
    bigint length(string str)
用途：返回字符串str的長度。
參數說明：
str：String類型，若輸入為bigint，double或datetime類型會隱式轉換為string後參與運算，其它類型報異常。
返回值：Bigint類型。若str是NULL返回NULL。如果str非UTF-8編碼格式，返回-1。
示例
    length('hi! 中國') = 6
LENGTHB
函數聲明：
    bigint lengthb(string str)
用途：返回字符串str的以字節為單位的長度。
參數說明：
str：String類型，若輸入為bigint，double或者datetime類型會隱式轉換為string後參與運算，其它類型報異常。
返回值：Bigint類型。若str是NULL返回NULL。
示例
    lengthb('hi! 中國') = 10
MD5
函數聲明：
    string md5(string value)
用途：計算輸入字符串value的md5值
參數說明：
value：String類型，如果輸入類型是bigint，double或者datetime會隱式轉換成string類型參與運算，其它類型報異常。輸入為NULL，返回NULL。
返回值：String類型。
REGEXP_EXTRACT
函數聲明：
    string regexp_extract(string source, string pattern[, bigint occurrence])
用途：將字符串source按照pattern正則表達式的規則拆分，返回第occurrence個group的字符。
參數說明：
source：String類型，待搜索的字符串。
pattern：String類型常量，pattern為空串時拋異常，pattern中如果沒有指定group，拋異常。
occurrence：Bigint類型常量，必須>=0，其它類型或小於0時拋異常，不指定時默認為1，表示返回第一個group。若occurrence = 0，返回滿足整個pattern的子串。
返回值：String類型，任一輸入為NULL返回NULL。
示例：
    regexp_extract('foothebar', 'foo(.*?)(bar)', 1) = the
    regexp_extract('foothebar', 'foo(.*?)(bar)', 2) = bar
    regexp_extract('foothebar', 'foo(.*?)(bar)', 0) = foothebar
    regext_extract('8d99d8', '8d(\d+)d8') = 99
    -- 如果是在MaxCompute客戶端上提交正則計算的SQL，需要使用兩個""作為轉移字符
    regexp_extract('foothebar', 'foothebar')
    -- 異常返回，pattern中沒有指定group
REGEXP_INSTR
命令格式：

    bigint regexp_instr(string source, string pattern[,
        bigint start_position[, bigint nth_occurrence[, bigint return_option]])
用途：返回字符串source從start_position開始，和pattern第n次(nth_occurrence)匹配的子串的起始/結束位置。任一輸入參數為NULL時返回NULL。
參數說明：
source：String類型，待搜索的字符串。
pattern：String類型常量，pattern為空串時拋異常。
start_position：Bigint類型常量，搜索的開始位置。不指定時默認值為1，其它類型或小於等於0的值會拋異常。
nth_occurrence：Bigint類型常量，不指定時默認值為1，表示搜索第一次出現的位置。小於等於0或者其它類型拋異常。
return_option：Bigint類型常量，值為0或1，其它類型或不允許的值會拋異常。0表示返回匹配的開始位置，1表示返回匹配的結束位置。
返回值：Bigint類型。視return_option指定的類型返回匹配的子串在source中的開始或結束位置。
示例：
    regexp_instr("i love www.taobao.com", "o[[:alpha:]]{1}", 3, 2) = 14
REGEXP_REPLACE
函數聲明：
    string regexp_replace(string source, string pattern, string replace_string[, bigint occurrence])
用途：將source字符串中第occurrence次匹配pattern的子串替換成指定字符串replace_string後返回。
參數說明：
source：String類型，要替換的字符串。
pattern：String類型常量，要匹配的模式，pattern為空串時拋異常。
replace_string：String類型，將匹配的pattern替換成的字符串。
occurrence：Bigint類型常量，必須大於等於0，表示將第幾次匹配替換成replace_string，為0時表示替換掉所有的匹配子串。其它類型或小於0拋異常。可缺省，默認值為0。
返回值：String類型，當引用不存在的組時，不進行替換。當輸入source，pattern，occurrence參數為NULL時返回NULL，若replace_string為NULL且pattern有匹配，返回NULL，replace_string為NULL但pattern不匹配，則返回原串 。

備注： 
當引用不存在的組時，行為未定義。
示例：
    regexp_replace("123.456.7890", "([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})",
        "(\1)\2-\3", 0) = "(123)456-7890"
    regexp_replace("abcd", "(.)", "\1 ", 0) = "a b c d "
    regexp_replace("abcd", "(.)", "\1 ", 1) = "a bcd"
    regexp_replace("abcd", "(.)", "\2", 1) = "abcd"
    -- 因為pattern中隻定義了一個組，引用的第二個組不存在，
    -- 請避免這樣使用，引用不存在的組的結果未定義。
    regexp_replace("abcd", "(.*)(.)$", "\2", 0) = "d"
    regexp_replace("abcd", "a", "\1", 0) = "bcd"
    -- 因為在pattern中沒有組的定義，所以1引用了不存在的組，
    -- 請避免這樣使用，引用不存在的組的結果未定義。
REGEXP_SUBSTR
函數聲明：
    string regexp_substr(string source, string pattern[, bigint start_position[, bigint nth_occurrence]])
用途：從start_position位置開始，source中第nth_occurrence次匹配指定模式pattern的子串。
參數說明：
source：String類型，搜索的字符串。
pattern：String類型常量，要匹配的模型，pattern為空串時拋異常。
start_position：Bigint常量，必須大於0。其它類型或小於等於0時拋異常，不指定時默認為1，表示從source的第一個字符開始匹配。不指定時默認為1，表示從source的第一個字符開始匹配。
nth_occurrence：Bigint常量，必須大於0，其它類型或小於等於0時拋異常。不指定時默認為1，表示返回第一次匹配的子串。不指定時默認為1，表示返回第一次匹配的子串。
返回值：String類型。任一輸入參數為NULL返回NULL。沒有匹配時返回NULL。
示例：
    regexp_substr ("I love aliyun very much", "a[[:alpha:]]{5}") = "aliyun"
    regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) = " have"
    regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2) = " 2"
REGEXP_COUNT
函數聲明：
    bigint regexp_count(string source, string pattern[, bigint start_position])
用途：計算source中從start_position開始，匹配指定模式pattern的子串的次數。
參數說明：
source：String類型，搜索的字符串，其它類型報異常。
pattern：String類型常量，要匹配的模型，pattern為空串時拋異常，其它類型報異常。
start_position：Bigint類型常量，必須大於0。其它類型或小於等於0時拋異常，不指定時默認為1，表示從source的第一個字符開始匹配。
返回值：Bigint類型。沒有匹配時返回0。任一輸入參數為NULL返回NULL。
示例：
    regexp_count('abababc', 'a.c') = 1
    regexp_count('abcde', '[[:alpha:]]{2}', 3) = 1
SPLIT_PART
函數聲明：
    string split_part(string str, string separator, bigint start[, bigint end])
用途：依照分隔符separator拆分字符串str，返回從第start部分到第end部分的子串(閉區間)。
參數說明：
Str：String類型，要拆分的字符串。如果是bigint，double或者datetime類型會隱式轉換到string類型後參加運算，其它類型報異常。
Separator：String類型常量，拆分用的分隔符，可以是一個字符，也可以是一個字符串，其它類型會引發異常。
start：Bigint類型常量，必須大於0。非常量或其它類型拋異常。返回段的開始編號(從1開始)，如果沒有指定end，則返回start指定的段。
end：Bigint類型常量，大於等於start，否則拋異常。返回段的截止編號，非常量或其他類型會引發異常。可省略，缺省時表示最後一部分。
返回值：String類型。若任意參數為NULL，返回NULL；若separator為空串，返回原字符串str。

備注:
如果separator不存在於str中，且start指定為1，返回整個str。若輸入為空串，輸出為空串。
如果start的值大於切分後實際的分段數，例如：字符串拆分完有6個片段，但start大於6，返回空串””。
若end大於片段個數，按片段個數處理。
示例：
    split_part('a,b,c,d', ',', 1) = 'a'
    split_part('a,b,c,d', ',', 1, 2) = 'a,b'
    split_part('a,b,c,d', ',', 10) =  ''
SUBSTR
函數聲明：
    string substr(string str, bigint start_position[, bigint length])
用途：返回字符串str從start_position開始往後數，長度為length的子串。
參數說明：
str：String類型，若輸入為bigint，decimal，double或者datetime類型會隱式轉換為string後參與運算，其它類型報異常。
start_position：Bigint類型，起始位置為1。當start_position為負數時表示開始位置是從字符串的結尾往前倒數，最後一個字符是-1，往前數依次就是-2，-3…，其它類型拋異常。
length：Bigint類型，大於0，其它類型或小於等於0拋異常。子串的長度。
返回值：String類型。若任一輸入為NULL，返回NULL。

備注 :
當length被省略時，返回到str結尾的子串。
示例
    substr("abc", 2) = "bc"
    substr("abc", 2, 1) = "b"
    substr("abc",-2,2")="bc"
    substr("abc",-3)="abc"
TOLOWER
函數聲明：
    string tolower(string source)
用途：輸出英文字符串source對應的小寫字符串。
參數說明：
source：String類型，若輸入為bigint，double或者datetime類型會隱式轉換為string後參與運算，其它類型報異常。
返回值：String類型。輸入為NULL時返回NULL。
示例：
    tolower("aBcd") = "abcd"
    tolower("哈哈Cd") = "哈哈cd"
TOUPPER
函數聲明：
    string toupper(string source)
用途：輸出英文字符source串對應的大寫字符串。
參數說明：
source：String類型，若輸入為bigint，double或者datetime類型會隱式轉換為string後參與運算，其它類型報異常。
返回值：String類型。輸入為NULL時返回NULL。
示例：
    toupper("aBcd") = "ABCD"
    toupper("哈哈Cd") = "哈哈CD"
TO_CHAR
函數聲明：
    string to_char(boolean value)
    string to_char(bigint value)
    string to_char(double value)
用途：將Boolean類型、bigint類型或者double類型轉為對應的string類型表示
參數說明：
value：可以接受boolean類型、bigint類型或者double類型輸入，其它類型拋異常。對datetime類型的格式化輸出請參考另一同名函數 TO_CHAR 。
返回值：String類型。如果輸入為NULL，返回NULL。
示例：
    to_char(123) = '123'
    to_char(true) = 'TRUE'
    to_char(1.23) = '1.23'
    to_char(null) = NULL
TRIM
函數聲明：
    string trim(string str)
用途：將輸入字符串str去除左右空格。
參數說明：
str：String類型，若輸入為bigint，double或者datetime類型會隱式轉換為string後參與運算，其它類型報異常。
返回值：String類型。輸入為NULL時返回NULL。 
LTRIM
函數聲明：
 string ltrim(string str)
用途：將輸入的字符串str去除左邊空格。 
參數說明： 
str：String類型，若輸入為bigint，decimal，double或者datetime類型會隱式轉換為string後參與運算，其它類型報異常。返回值：String類型。輸入為NULL時返回NULL。 
示例:
select ltrim(' abc ') from dual;  
返回：  
+-----+
| _c0 |
+-----+
| abc  |
+-----+
RTRIM
函數聲明：
 string rtrim(string str)
用途：將輸入的字符串str去除右邊空格。 
參數說明： 
str：String類型，若輸入為bigint，decimal，double或者datetime類型會隱式轉換為string後參與運算，其它類型報異常。返回值：String類型。輸入為NULL時返回NULL。 
示例:
select rtrim('a abc ') from dual;  
返回：  
+-----+
| _c0 |
+-----+
| a abc |
+-----+
REVERSE
函數申明： 
 STRING REVERSE(string str)
用途：返回倒序字符串。參數說明： 
str：String類型，若輸入為bigint，double，decimal或datetime類型會隱式轉換為string後參與運算，其它類型報異常。返回值：String類型。輸入為NULL時返回NULL。 
示例： 
select reverse('abcedfg') from dual;
返回：  
+-----+
| _c0 |
+-----+
| gfdecba |
+-----+
SPACE
函數聲明：

STRING SPACE(bigint n)
用途：空格字符串函數，返回長度為n的字符串。參數說明： 
n: bigint類型。長度不超過2M。如果為空，則拋異常。返回值：String類型。
示例： 
select length(space(10)) from dual;    ----返回10。  
select space(400000000000) from dual；   ----報錯，長度超過2M。
REPEAT
函數聲明：
STRING REPEAT(string str, bigint n)
用途：返回重複n次後的str字符串。參數說明： 
str：String類型，若輸入為bigint，double，decimal或datetime類型會隱式轉換為string後參與運算，其它類型報異常。 
n: Bigint類型。長度不超過2M。如果為空，則拋異常。返回值：String類型。 
示例： 
 select repeat('abc',5) from lxw_dual;  
 返回：abcabcabcabcabc
ASCII
函數聲明： 
Bigint ASCII(string str)
用途：返回字符串str第一個字符的ascii碼。參數說明： 
str：String類型，若輸入為bigint，double，decimal或datetime類型會隱式轉換為string後參與運算，其它類型報異常。返回值：Bigint類型。 
示例： 
select ascii('abcde') from dual;  
返回值：97
日期函數
MaxCompute SQL提供了針對datetime類型的操作函數。
DATEADD
函數聲明：
    datetime dateadd(datetime date, bigint delta, string datepart)
用途：按照指定的單位datepart和幅度delta修改date的值。
參數說明：
date：Datetime類型，日期值。若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。。
delta：Bigint類型，修改幅度。若輸入為string類型或double型會隱式轉換到bigint類型後參與運算，其他類型會引發異常。若delta大於0，加；否則減。
datepart：String類型常量。此字段的取值遵循string與datetime類型轉換的約定，即”yyyy”表示年，”mm”表示月….關於類型轉換的規則請參考 String類型與Datetime類型之間的轉換 。此外也支持擴展的日期格式：年-“year”，月-“month”或”mon”，日-“day”，小時-“hour”。非常量、不支持的格式會或其它類型拋異常。
返回值：Datetime類型。若任一輸入參數為NULL，返回NULL。

備注:
按照指定的單位增減delta時導致的對更高單位的進位或退位，年、月、時、分、秒分別按照10進製、12進製、24進製、60進製、60進製計算。當delta的單位是月時，計算規則如下：若datetime的月部分在增加delta值之後不造成day溢出，則保持day值不變，否則把day值設置為結果月份的最後一天。
datepart的取值遵循string與datetime類型轉換的約定，即”yyyy”表示年，”mm”表示月…. datetime相關的內建函數如無特殊說明均遵守此約定。同時如果沒有特殊說明，所有datetime相關的內建函數的part部分也同樣支持擴展的日期格式：年-“year”，月-“month”或”mon”，日-“day”，小時-“hour”。
示例：
    若trans_date = 2005-02-28 00:00:00：
    dateadd(trans_date, 1, 'dd') = 2005-03-01 00:00:00
        -- 加一天，結果超出當年2月份的最後一天，實際值為下個月的第一天
    dateadd(trans_date, -1, 'dd') = 2005-02-27 00:00:00
        -- 減一天
    dateadd(trans_date, 20, 'mm') = 2006-10-28 00:00:00
        -- 加20個月，月份溢出，年份加1
    若trans_date = 2005-02-28 00:00:00, dateadd(transdate, 1, 'mm') = 2005-03-28 00:00:00
    若trans_date = 2005-01-29 00:00:00, dateadd(transdate, 1, 'mm') = 2005-02-28 00:00:00
        -- 2005年2月沒有29日，日期截取至當月最後一天
    若trans_date = 2005-03-30 00:00:00, dateadd(transdate, -1, 'mm') = 2005-02-28 00:00:00
此處對trans_date的數值表示僅作示例使用，在文檔中有關datetime介紹會經常使用到這種簡易的表達方式。在MaxCompute SQL中，datetime類型沒有直接的常數表示方式，如下使用方式是錯誤的：

    select dateadd(2005-03-30 00:00:00, -1, 'mm') from tbl1;
如果一定要描述datetime類型常量，請嚐試如下方法：
    select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm') from tbl1;
        -- 將String類型常量顯式轉換為Datetime類型
DATEDIFF
命令格式：
    bigint datediff(datetime date1, datetime date2, string datepart)
用途：計算兩個時間date1，date2在指定時間單位datepart的差值。
參數說明：
datet1，date2：Datetime類型，被減數和減數，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。
datepart：String類型常量。支持擴展的日期格式。若datepart不符合指定格式或者其它類型則會發生異常。
返回值：Bigint類型。任一輸入參數是NULL，返回NULL。如果date1小於date2，返回值可以為負數。 

備注 :
計算時會按照datepart切掉低單位部分，然後再計算結果。
示例：
    若start = 2005-12-31 23:59:59，end = 2006-01-01 00:00:00:
    datediff(end, start, 'dd') = 1
    datediff(end, start, 'mm') = 1
    datediff(end, start, 'yyyy') = 1
    datediff(end, start, 'hh') = 1
    datediff(end, start, 'mi') = 1
    datediff(end, start, 'ss') = 1
    datediff(2013-05-31 13:00:00, 2013-05-31 12:30:00, 'ss') = 1800
    datediff(2013-05-31 13:00:00, 2013-05-31 12:30:00, 'mi') = 30
DATEPART
函數聲明：

    bigint datepart(datetime date, string datepart)
用途：提取日期date中指定的時間單位datepart的值。
參數說明：
date：Datetime類型，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。
datepart：String類型常量。支持擴展的日期格式。若datepart不符合指定格式或者其它類型則會發生異常。
返回值：Bigint類型。若任一輸入參數為NULL，返回NULL。
示例：
    datepart('2013-06-08 01:10:00', 'yyyy')  =  2013
    datepart('2013-06-08 01:10:00', 'mm')  =  6
DATETRUNC
函數聲明：
    datetime datetrunc (datetime date, string datepart)
用途：返回日期date被截取指定時間單位datepart後的日期值。
參數說明：
date：Datetime類型，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。
datepart：String類型常量。支持擴展的日期格式。若datepartt不符合指定格式或者其它類型則會發生異常。
返回值：Datetime類型。任意一個參數為NULL的時候返回NULL。
示例：
    datetrunc(2011-12-07 16:28:46, 'yyyy') = 2011-01-01 00:00:00
    datetrunc(2011-12-07 16:28:46, 'month') = 2011-12-01 00:00:00
    datetrunc(2011-12-07 16:28:46, 'DD') = 2011-12-07 00:00:00
FROM_UNIXTIME
函數聲明：
    datetime from_unixtime(bigint unixtime)
用途：將數字型的unix時間日期值unixtime轉為日期值。
參數說明：
unixtime：Bigint類型，秒數，unix格式的日期時間值，若輸入為string，double類型會隱式轉換為bigint後參與運算。
返回值：Datetime類型的日期值，unixtime為NULL時返回NULL。
示例：
    from_unixtime(123456789) = 2009-01-20 21:06:29
GETDATE
函數聲明：
    datetime getdate()
用途：獲取當前係統時間。使用東八區時間作為MaxCompute標準時間。
返回值：返回當前日期和時間，datetime類型。

備注： 
在一個MaxCompute SQL任務中(以分布式方式執行)，getdate總是返回一個固定的值。返回結果會是MaxCompute SQL執行期間的任意時間，時間精度精確到秒。
ISDATE
函數聲明：
    boolean isdate(string date, string format)
用途：判斷一個日期字符串能否根據對應的格式串轉換為一個日期值，如果轉換成功返回TRUE，否則返回FALSE。
參數說明：
date：String格式的日期值，若輸入為bigint，double或者datetime類型會隱式轉換為string類型後參與運算，其它類型報異常。
format：String類型常量，不支持日期擴展格式。其它類型或不支持的格式會拋異常。如果format中出現多餘的格式串，則隻取第一個格式串對應的日期數值，其餘的會被視為分隔符。如isdate(“1234-yyyy “, “yyyy-yyyy “)，會返回TRUE。
返回值：Boolean類型，如任意參數為NULL，返回NULL。
LASTDAY
函數聲明：
    datetime lastday(datetime date)
用途：取date當月的最後一天，截取到天，時分秒部分為00:00:00。
參數說明：
date：Datetime類型，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型報異常。
返回值：Datetime類型，如輸入為NULL，返回NULL
TO_DATE
函數聲明：
    datetime to_date(string date, string format)
用途：將一個字符串date按照format指定的格式轉成日期值。
參數說明：
date：String類型，要轉換的字符串格式的日期值，若輸入為bigint，double或者datetime類型會隱式轉換為String類型後參與運算，為其它類型拋異常，為空串時拋異常。
format：String類型常量，日期格式。非常量或其他類型會引發異常。format不支持日期擴展格式，其他字符作為無用字符在解析時忽略。format參數至少包含”yyyy”，否則引發異常，如果format中出現多餘的格式串，則隻取第一個格式串對應的日期數值，其餘的會被視為分隔符。如to_date(“1234-2234 “, “yyyy-yyyy “)會返回1234-01-01 00:00:00。
返回值：Datetime類型。若任一輸入為NULL，返回NULL值。
示例：
    to_date('阿裏巴巴2010-12*03', '阿裏巴巴yyyy-mm*dd') = 2010-12-03 00:00:00
    to_date('20080718', 'yyyymmdd') = 2008-07-18 00:00:00
    to_date('2008718', 'yyyymmdd')
    -- 格式不符合，引發異常
    to_date('阿裏巴巴2010-12*3', '阿裏巴巴yyyy-mm*dd')
    -- 格式不符合，引發異常
    to_date('2010-24-01', 'yyyy')
    -- 格式不符合，引發異常
TO_CHAR
函數聲明：

    string to_char(datetime date, string format)
用途：將日期類型date按照format指定的格式轉成字符串
參數類型：
date：Datetime類型，要轉換的日期值，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。
format：String類型常量。非常量或其他類型會引發異常。format中的日期格式部分會被替換成相應的數據，其它字符直接輸出。
返回值：String類型。任一輸入參數為NULL，返回NULL。
示例：
    to_char('2010-12-03 00:00:00', '阿裏金融yyyy-mm*dd') = '阿裏金融2010-12*03'
    to_char('2008-07-18 00:00:00', 'yyyymmdd') = '20080718' 
    to_char('阿裏巴巴2010-12*3', '阿裏巴巴yyyy-mm*dd') -- 引發異常
    to_char('2010-24-01', 'yyyy') -- 會引發異常
    to_char('2008718', 'yyyymmdd') -- 會引發異常

備注: 
關於其他類型向string類型轉換請參考 字符串函數 TO_CHAR 。

UNIX_TIMESTAMP
函數聲明：
    bigint unix_timestamp(datetime date)
用途：將日期date轉化為整型的unix格式的日期時間值。
參數說明：
date：Datetime類型日期值，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。
返回值：Bigint類型，表示unix格式日期值，date為NULL時返回NULL。
WEEKDAY
函數聲明：
    bigint weekday (datetime date)
用途：返回date日期當前周的第幾天。
參數說明：
date：Datetime類型，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。 
返回值：Bigint類型，若輸入參數為NULL，返回NULL。周一作為一周的第一天，返回值為0。其他日期依次遞增，周日返回6。 
WEEKOFYEAR
函數聲明：
bigint weekofyear(datetime date)
用途：返回日期date位於那一年的第幾周。周一作為一周的第一天。需要注意的是，關於這一周算上一年， 還是下一年，主要是看這一周大多數日期（4天以上）在哪一年多。 算在前一年，就是前一年的最後一周。算在後一年就是後一年的第一周。 

參數說明: 
date：Datetime類型日期值，若輸入為string類型會隱式轉換為datetime類型後參與運算，其它類型拋異常。 
返回值：Bigint類型。若輸入為NULL，返回NULL。 
示例說明： 
select weekofyear(to_date("20141229", "yyyymmdd")) from dual;  
返回結果：  
+------------+
| _c0        |
+------------+
| 1          |
+------------+
 -雖然20141229屬於2014年，但是這一周的大多數日期是在2015年，因此返回結果為1，表示是2015年的第一周。    
 select weekofyear(to_date("20141231", "yyyymmdd")) from dual；--返回結果為1。  
 select weekofyear(to_date("20151229", "yyyymmdd")) from dual；--返回結果為53。
 最後更新：2016-11-23 17:16:04
  上一篇： DML語句__SQL_大數據計算服務-阿裏雲
  下一篇： 內建函數-下__SQL_大數據計算服務-阿裏雲
相關內容
 RAM中可授權的專有網絡資源類型__借助RAM實現子賬號對主賬號的VPC資源訪問_API參考_專有網絡 VPC-阿裏雲
 vpc下內網隔離設置__常見問題_專有網絡 VPC-阿裏雲
 Oracle數據源配置__數據源配置_數據同步手冊_用戶操作指南_大數據開發套件-阿裏雲
 阿裏雲技術與生態合作論壇等你來撩！
 ALIYUN::ECS::VSwitch__資源列表_資源編排-阿裏雲
 MapReduce__概要__大數據計算服務-阿裏雲
 集群詳情頁__集群_用戶指南_E-MapReduce-阿裏雲
 3.4 列的類型和屬性__第三章 DDL_使用手冊_分析型數據庫-阿裏雲
 查看應用實例__應用API列表_API參考_容器服務-阿裏雲
 編輯索引__結構管理_DMS for MongoDB_用戶指南（NoSQL）_數據管理-阿裏雲
熱門內容
 常見錯誤說明__附錄_大數據計算服務-阿裏雲
 發送短信接口__API使用手冊_短信服務-阿裏雲
 接口文檔__Android_安全組件教程_移動安全-阿裏雲
 運營商錯誤碼（聯通）__常見問題_短信服務-阿裏雲
 設置短信模板__使用手冊_短信服務-阿裏雲
 OSS 權限問題及排查__常見錯誤及排除_最佳實踐_對象存儲 OSS-阿裏雲
 消息通知__操作指南_批量計算-阿裏雲
 設備端快速接入(MQTT)__快速開始_阿裏雲物聯網套件-阿裏雲
 查詢API調用流量數據__API管理相關接口_API_API 網關-阿裏雲
 使用STS訪問__JavaScript-SDK_SDK 參考_對象存儲 OSS-阿裏雲
最新內容
 阿裏雲小站論壇快速訪問指南及常見問題解答
 京東雲與阿裏雲深度對比：哪家更適合你？
 阿裏雲對象存儲OSS文件刪除詳解：高效、安全地管理您的數據
 阿裏雲盤高效緩存電視劇的技巧與方法
 阿裏雲盤本地文件夾如何設置及高級技巧詳解
 阿裏雲普通用戶詳細收費指南：避坑指南與省錢技巧
 除了京東雲和阿裏雲，還有哪些雲計算服務商？深度解析國內外主流雲平台
 阿裏雲盤容量顯示不足或無容量的常見原因及解決方法
 阿裏雲的雲：深入解讀雲計算巨頭的技術基石
 阿裏雲原生服務實戰指南：從入門到進階

內建函數-上__SQL_大數據計算服務-阿裏雲

ABS

ACOS

ASIN

ATAN

CEIL

CONV

COS

COSH

COT

EXP

FLOOR

LN

LOG

POW

RAND

ROUND

SIGN

SIN

SINH

SQRT

TAN

TANH

TRUNC

字符串函數

CHAR_MATCHCOUNT

CHR

CONCAT

GET_JSON_OBJECT

INSTR

IS_ENCODING

KEYVALUE

LENGTH

LENGTHB

MD5

REGEXP_EXTRACT

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

REGEXP_COUNT

SPLIT_PART

SUBSTR

TOLOWER

TOUPPER

TO_CHAR

TRIM

LTRIM

RTRIM

REVERSE

SPACE

REPEAT

ASCII

日期函數

DATEADD

DATEDIFF

DATEPART

DATETRUNC

FROM_UNIXTIME

GETDATE

ISDATE

LASTDAY

TO_DATE

TO_CHAR

UNIX_TIMESTAMP

WEEKDAY

WEEKOFYEAR

上一篇： DML語句__SQL_大數據計算服務-阿裏雲

下一篇： 內建函數-下__SQL_大數據計算服務-阿裏雲

相關內容

熱門內容

最新內容

`INSTR`

`REGEXP_INSTR`

`SPACE`

`DATEPART`

`TO_CHAR`

下一篇：內建函數-下__SQL_大數據計算服務-阿裏雲