全部产品
阿里云办公

字符串函数

更新时间:2017-10-17 17:20:31

Note:

  • 这些函数只处理UTF-8编码的可见字符,对输入字符串不作编码格式检查,非法的字符或字符串会导致查询结果异常。

  • lower() 和 upper() 函数只处理英文字母的大小写转换,这对中文等非拉丁语言会返回错误结果。

chr(n) → varchar

返回下标为 n 位置的字符。

concat(string1, …, stringN) → varchar

字符串连接操作,返回 string1 , string2 , … , stringN 字符串连接。 此功能与标准SQL的连接运算符 ( || )功能相同。

length(string) → bigint

返回字符串 string 长度。

lower(string) → varchar

转换字符串 string 为小写。

lpad(string, size, padstring) → varchar

将字符串 string 左边拼接 padstring 直到长度达到 size 并返回填充后的字符串。如果 size 比 string 长度小,则截断。 size 不能为负数, padstring 必须非空。

ltrim(string) → varchar

删除字符串所有前导空格。

replace(string, search) → varchar

删除字符串 string 中的所有子串 search 。

replace(string, search, replace) → varchar

将字符串 string 中所有子串 search 替换为 replace。

Left/Right(str,len)

返回从字符串str 开始的len 最左/右字符

reverse(string) → varchar

将字符串 string 逆序后返回。

rpad(string, size, padstring) → varchar

将字符串 string 右边拼接 padstring 直到长度达到 size ,返回填充后的字符串。如果 size 比 string 长度小,则截断。 size 不能为负数, padstring 必须非空。

rtrim(string) → varchar

删除字符串 string 右边所有空格。

split(string, delimiter) → array<varchar>

将字符串 string 按分隔符 delimiter 进行分隔,并返回数组。

split(string, delimiter, limit) → array<varchar>

将字符串 string 按分隔符 delimiter 分隔,并返回按 limit 大小限制的数组。数组中的最后一个元素包含字符串中的所有剩余内容。 limit 必须是正数。

split_part(string, delimiter, index) → varchar

将字符串 string 按分隔符 delimiter 分隔,并返回分隔后数组下标为 index 的子串。 index 以 1 开头,如果大于字段数则返回null。

split_to_map(string, entryDelimiter, keyValueDelimiter) → map<varchar, varchar>

通过 entryDelimiter 和 keyValueDelimiter 拆分字符串并返回map。 entryDelimiter 将字符串分解为key-value对, keyValueDelimiter 将每对分隔成key、value。

strpos(string, substring) → bigint

返回字符串中子字符串的第一次出现的起始位置。位置以 1 开始 ,如果未找到则返回 0 。

position(substring IN string) → bigint

返回字符串中子字符串的第一次出现的起始位置。位置以 1 开始,如果未找到则返回 0 。

substr(string, start) → varchar

返回 start 位置开始到字符串结束。位置从 1 开始。如果 start 为负数,则起始位置代表从字符串的末尾开始倒数。

substr(string, start, length) → varchar

返回 start 位置开始长度为 length 的子串,位置从 1 开始。如果 start 为负数,则起始位置代表从字符串的末尾开始倒数。

trim(string) → varchar

删除字符串 string 前后的空格。

upper(string) → varchar

转换字符串为大写

uuid()

返回一个字符串,在当前集群内保证唯一,算法参考mongodb的objectid实现

Unicode 函数

normalize(string) → varchar

用NFC规范化形式转换字符串。

normalize(string, form) → varchar

使用指定的规范化形式转换字符串。 form 必须是以下关键字之一:

Form 解释
NFD 规范分解
NFC 规范分解,其次是规范组合
NFKD 兼容性分解
NFKC 兼容性分解,其次是规范组合

Note:

  • SQL标准函数有特殊的语法,需要将 form 指定为关键字,而不是字符串。

to_utf8(string) → varbinary

将字符串编码为UTF-8格式。

from_utf8(binary) → varchar

将二进制 binary 解码为UTF-8编码的字符串,无效的UTF-8字符被替换为Unicode字符 U+FFFD 。

from_utf8(binary, replace) → varchar

将二进制 binary 解码为UTF-8编码的字符串。无效的UTF-8字符替换为 replace 。替换字符串必须是单个字符或空格(无效字符会被删除)。

char2hexint(string) → varchar

返回字符串的UTF-16BE编码的十六进制表示形式.

index(string, substring) → bigint

同 strpos() function.

substring(string, start) → varchar

同 substr() function.

substring(string, start, length) → varchar

同 substr() function.

String 函数MySQL兼容性(ADS 已经支持的MySQL函数)

ASCII

Returns the numeric value of the leftmost character of the string str.

支持的语法:

ASCII(str)

例子:

  1. SELECT ASCII('2');
  2. SELECT ASCII(2);
  3. SELECT ASCII('dx');

BIN

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number.

支持的语法:

BIN(N)

例子:

  1. SELECT BIN(12);

CHAR

CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

支持的语法:

CHAR(N,...)

例子:

  1. SELECT CHAR(77,121,83,81,76);

CHAR_LENGTH

Returns the length of the string str, measured in characters. A multibyte character counts as a single character.

支持的语法:

CHAR_LENGTH(str)

例子:

  1. SELECT CHAR_LENGTH('111');

CHARACTER_LENGTH

Returns the length of the string str, measured in characters. A multibyte character counts as a single character.

支持的语法:

CHARACTER_LENGTH(str)

例子:

  1. SELECT CHARACTER_LENGTH('111');

EXPORT_SET

Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string.

支持的语法:

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

例子:

  1. SELECT EXPORT_SET(5,'Y','N',',',4);
  2. SELECT EXPORT_SET(6,'1','0',',',10);

FIND_IN_SET

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

支持的语法:

FIND_IN_SET(str,strlist)

例子:

  1. SELECT FIND_IN_SET('b','a,b,c,d');

FORMAT

Formats the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the result as a string.

支持的语法:

FORMAT(X,D[,locale])

例子:

  1. SELECT FORMAT(12332.123456, 4);
  2. SELECT FORMAT(12332.1, 4);
  3. SELECT FORMAT(12332.2,0);
  4. SELECT FORMAT(12332.2,2,'de_DE');

FROM_BASE64

Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string.

支持的语法:

FROM_BASE64(str)

例子:

  1. SELECT TO_BASE64('abc');
  2. SELECT FROM_BASE64_MYSQL(TO_BASE64('abc'));

HEX

For a string argument str, HEX() returns a hexadecimal string representation of str where each byte of each character in str is converted to two hexadecimal digits.

支持的语法:

HEX(str) , HEX(N)

例子:

  1. SELECT HEX('abc');
  2. SELECT HEX(255);
  3. SELECT UNHEX_MYSQL(HEX('abc'));

INSERT

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.

支持的语法:

INSERT(str,pos,len,newstr)

例子:

  1. SELECT PRESTO_INSERT('Quadratic', 3, 4, 'What');
  2. SELECT PRESTO_INSERT('Quadratic', -1, 4, 'What');
  3. SELECT PRESTO_INSERT('Quadratic', 3, 100, 'What');

INSTR

Returns the position of the first occurrence of substring substr in string str.

支持的语法:

INSTR(str,substr)

例子:

SELECT INSTR('foobarbar', 'bar'); SELECT INSTR('xbar', 'foobar');

LCASE

LCASE() is a synonym for LOWER().

支持的语法:

LCASE(str)

例子:

  1. SELECT LCASE('FoOBAr');
  2. SELECT Lower('FoOBAr');

Ucase

返回字符串的全大写

UPPER

UPPER() is a synonym for UCASE().

支持的语法:

UPPER(str)

例子:

  1. SELECT UPPER('FoOBAr');
  2. SELECT UCASE('FoOBAr');

LEFT

Returns the leftmost len characters from the string str, or NULL if any argument is NULL

支持的语法:

LEFT(str,len)

例子:

  1. SELECT PRESTO_LEFT('foobarbar', 5);

LENGTH

Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes.

支持的语法:

LENGTH(str)

例子:

  1. SELECT LENGTH('text');

LOCATE

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos.

支持的语法:

LOCATE(substr,str) , LOCATE(substr,str,pos)

例子:

  1. SELECT LOCATE('bar', 'foobarbar');
  2. SELECT LOCATE('xbar', 'foobar');
  3. SELECT LOCATE('xbar', 'foobar');

LPAD

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

支持的语法:

LPAD(str,len,padstr)

例子:

  1. SELECT LPAD('hi',4,'??');
  2. SELECT LPAD('hi',1,'??');

LTRIM

Returns the string str with leading space characters removed.

支持的语法:

LTRIM(str)

例子:

  1. SELECT LTRIM(' foOBarBaR');

SUBSTRING

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. SUBSTR() is a synonym for SUBSTRING().

支持的语法:

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) , SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

例子:

  1. SELECT SUBSTRING('Quadratically',5);
  2. SELECT SUBSTRING('foobarbar' FROM 4);
  3. SELECT SUBSTRING('Quadratically',5,6);
  4. SELECT SUBSTRING('Sakila', -3);
  5. SELECT SUBSTRING('Sakila', -5, 3);
  6. SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
  7. SELECT SUBSTR('Quadratically',5);
  8. SELECT SUBSTR('Quadratically',5,6);
  9. SELECT SUBSTR('Sakila', -3);
  10. SELECT SUBSTR('Sakila', -5, 3)

MID

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

支持的语法:

MID(str,pos,len)

例子:

  1. SELECT mid('Quadratically',5,6);

OCT

Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number.

支持的语法:

OCT(N)

例子:

  1. SELECT OCT(12);

ORD

If the leftmost character of the string str is a multibyte character, returns the code for that character.

支持的语法:

ORD(str)

例子:

  1. SELECT ORD('2');

REPEAT

Returns a string consisting of the string str repeated count times.

支持的语法:

REPEAT(str,count)

例子:

  1. SELECT REPEAT('MySQL', 3);

REPLACE

Returns the string str with all occurrences of the string from_str replaced by the string to_str.

支持的语法:

REPLACE(str,from_str,to_str)

例子:

  1. SELECT REPLACE('www.mysql.com', 'w', 'Ww');

REVERSE

Returns the string str with the order of the characters reversed.

支持的语法:

REVERSE(str)

例子:

  1. SELECT REVERSE('abc');

RIGHT

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

支持的语法:

RIGHT(str,len)

例子:

  1. SELECT PRESTO_RIGHT('foobarbar', 4);

RPAD

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

支持的语法:

RPAD(str,len,padstr)

例子:

  1. SELECT RPAD('hi',5,'?');
  2. SELECT RPAD('hi',1,'?');

RTRIM

Returns the string str with trailing space characters removed.

支持的语法:

RTRIM(str)

例子:

  1. SELECT RTRIM('barbar ');

SOUNDEX

Returns a soundex string from str.

支持的语法:

SOUNDEX(str)

例子:

  1. SELECT SOUNDEX('Hello');
  2. SELECT SOUNDEX('Quadratically');

SPACE

Returns a string consisting of N space characters.

支持的语法:

SPACE(N)

例子:

  1. SELECT SPACE(6);

SUBSTRING_INDEX

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

支持的语法:

SUBSTRING_INDEX(str,delim,count)

例子:

  1. SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
  2. SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

TRIM

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

支持的语法:

TRIM(str)

例子:

  1. SELECT TRIM(' bar ');

ip2region

Returns the region of the given ip address. You can specify the level as ‘COUNTRY’, ‘AREA’, ‘PROVINCE’, ‘CITY’, ‘ISP’.

支持的语法:

ip2region(ip_address, level)

例子:

  1. SELECT ip2region('101.105.35.57', 'COUNTRY');
  2. SELECT ip2region('101.105.35.57', 'Province');
  3. SELECT ip2region('101.105.35.57', 'CITY');

String函数Oracle兼容性(ADS 已经支持的Oracle函数)

INITCAP

INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase.

支持的语法:

INITCAP(char)

例子:

  1. SELECT INITCAP('the soap')
  2. SELECT INITCAP('the,soap')

INSTR

The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence.

支持的语法:

INSTR(string, substring) | INSTR(string, substring, position) | INSTR(string, substring, position, occurence)

例子:

  1. SELECT INSTR('CORPORATE FLOOR','OR');
  2. SELECT INSTR('CORPORATE FLOOR','OR', 3);
  3. SELECT INSTR('CORPORATE FLOOR','OR', 3, 2);

LPAD

The LPAD function returns an expression, left-padded to a specified length with the specified characters; or, when the expression to be padded is longer than the length specified after padding, only that portion of the expression that fits into the specified length.

支持的语法:

LPAD (text-exp , length) | LPAD (text-exp , length, pad-exp)

例子:

  1. SELECT LPAD('HELLO', 5);
  2. SELECT LPAD('HELLO', 5, 'A');

LTRIM

LTRIM removes from the left end of char all of the characters contained in set.

支持的语法:

LTRIM(char) | LTRIM(char, set)

例子:

  1. SELECT LTRIM(' WWW.TTTT');
  2. SELECT LTRIM('WWW.TTTT','W');

REGEXP_COUNT

REGEXP_COUNT complements the functionality of the REGEXP_INSTR function by returning the number of times a pattern occurs in a source string.

支持的语法:

REGEXP_COUNT(source_char, pattern)

例子:

  1. SELECT REGEXP_COUNT('rat cat\nbat dog', '.at');

REGEXP_SUBSTR

REGEXP_SUBSTR extends the functionality of the SUBSTR function by letting you search a string for a regular expression pattern.

支持的语法:

REGEXP_SUBSTR(source_char, pattern) | REGEXP_SUBSTR(source_char, pattern, position)

例子:

  1. REGEXP_SUBSTR('Hello world bye', '\\b[a-z]([a-z]*)');
  2. REGEXP_SUBSTR('Hello world bye', '\\b[a-z]([a-z]*)', 1);

REPLACE

REPLACE returns char with every occurrence of search_string replaced with replacement_string.

支持的语法:

REPLACE(char, search_string, replacement_string) | REPLACE(char, search_string)

例子:

  1. SELECT REPLACE('JACK and JUE','J','BL');
  2. SELECT REPLACE('JACK and JUE','J');

RPAD

RPAD returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary.

支持的语法:

RPAD (expr1, n, expr2) | RPAD (expr1, n)

例子:

  1. SELECT RPAD('HELLO', 5);
  2. SELECT RPAD('HELLO', 5, 'A');

RTRIM

RTRIM removes from the right end of char all of the characters that appear in set.

支持的语法:

RTRIM(char) | RTRIM(char, set)

例子:

  1. SELECT RTRIM('WWW.TTTT ');
  2. SELECT RTRIM('WWW.TTTT', 'T');

TRANSLATE

TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string.

支持的语法:

TRANSLATE(expr, from_string, to_string)

例子:

  1. SELECT TRANSLATE('acbd','ab','AB');
  2. SELECT TRANSLATE('acbd','abc','A');
  3. SELECT TRANSLATE('acbd','abc','');