# 字符串函数 #### 字符串函数 数据湖探索（DLI）提供了丰富的字符串函数，用于处理和转换字符串数据。这些函数包括拼接、大小写转换、截取子串、替换、正则匹配、编码解码、格式转换等。此外，还支持字符串长度计算、位置查找、填充、反转等功能，以及从JSON字符串中提取值的JSON_VAL函数。这些功能广泛应用于数据清洗、文本处理和数据分析场景，为开发者提供强大的工具支持。 字符串函数简介请参考[表1]，更多内容参考[Apache Flink](https://nightlies.apache.org/flink)。 表1字符串函数 | SQL函数 | 描述 | |:---|:---| | string1 \|\| string2 | 返回 STRING1 和 STRING2 的连接。 | | CHAR_LENGTH(string) CHARACTER_LENGTH(string) | 返回字符串中的字符数。 | | UPPER(string) | 以大写形式返回字符串。 | | LOWER(string) | 以小写形式返回字符串。 | | POSITION(string1 IN string2) | 返回 STRING2 中第一次出现 STRING1 的位置（从 1 开始）； 如果在 STRING2 中找不到 STRING1 返回 0。 | | TRIM(\[ BOTH \| LEADING \| TRAILING \] string1 FROM string2) | 返回从 STRING1 中删除以字符串 STRING2 开头/结尾/开头且结尾的字符串的结果。默认情况下，两边的空格都会被删除。 | | LTRIM(string) | 返回从 STRING 中删除左边空格的字符串。 例如 ' This is a test String.'.ltrim() 返回 'This is a test String.'。 | | RTRIM(string) | 返回从 STRING 中删除右边空格的字符串。 例如 'This is a test String. '.ltrim() 返回 'This is a test String.'。 | | REPEAT(string, int) | 返回 INT 个 string 连接的字符串。 例如 REPEAT('This is a test String.', 2) 返回 "This is a test String.This is a test String."。 | | REGEXP_REPLACE(string1, string2, string3) | 返回 STRING1 所有与正则表达式 STRING2 匹配的子字符串被 STRING3 替换后的字符串。 例如 'foobar'.regexpReplace('oo\|ar', '') 返回 "fb"。 | | OVERLAY(string1 PLACING string2 FROM integer1 \[ FOR integer2 \]) | 返回一个字符串，该字符串从位置 INT1 用 STRING2 替换 STRING1 的 INT2（默认为 STRING2 的长度）字符。 例如 'xxxxxtest'.overlay('xxxx', 6) 返回 "xxxxxxxxx"； 'xxxxxtest'.overlay('xxxx', 6, 2) 返回 "xxxxxxxxxst"。 | | SUBSTRING(string FROM integer1 \[ FOR integer2 \]) | 返回 STRING 从位置 INT1 开始，长度为 INT2（默认到结尾）的子字符串。 | | REPLACE(string1, string2, string3) | 返回一个新字符串，它用 STRING1 中的 STRING3（非重叠）替换所有出现的 STRING2。 例如 'hello world'.replace('world', 'flink') 返回 'hello flink'； 'ababab'.replace('abab', 'z') 返回 'zab'。 | | REGEXP_EXTRACT(string1, string2\[, integer\]) | 将字符串 STRING1 按照 STRING2 正则表达式的规则拆分，返回指定 INTEGER1 处位置的字符串。 正则表达式匹配组索引从 1 开始， 0 表示匹配整个正则表达式。此外，正则表达式匹配组索引不应超过定义的组数。 例如 REGEXP_EXTRACT('foothebar', 'foo(.\*?)(bar)', 2) 返回 "bar"。 | | INITCAP(string) | 返回新形式的 STRING，其中每个单词的第一个字符转换为大写，其余字符转换为小写。这里的单词表示字母数字的字符序列。 | | CONCAT(string1, string2, ...) | 返回连接 string1，string2， ... 的字符串。如果有任一参数为 NULL，则返回 NULL。 例如 CONCAT('AA', 'BB', 'CC') 返回 "AABBCC"。 | | CONCAT_WS(string1, string2, string3, ...) | 返回将 STRING2， STRING3， ... 与分隔符 STRING1 连接起来的字符串。 在要连接的字符串之间添加分隔符。 如果 STRING1 为 NULL，则返回 NULL。 与 concat() 相比，concat_ws() 会自动跳过 NULL 参数。 例如 concat_ws('\~', 'AA', Null(STRING), 'BB', '', 'CC') 返回 "AA\~BB\~\~CC". | | LPAD(string1, integer, string2) | 返回从 string1 靠左填充 string2 到 INT 长度的新字符串。 如果 string1 的长度小于 INT 值，则返回 string1 缩 短为整数字符。 例如 LPAD('hi', 4, '??') 返回 "??hi"；LPAD('hi', 1, '??') 返回 \`"h"。 | | RPAD(string1, integer, string2) | 返回从 string1 靠右边填充 string2 到 INT 长度的新字符串。 如果 string1 的长度小于 INT 值，则返回 string1 缩 短为长度为 INT 的新字符串。 例如 RPAD('hi', 4, '??') 返回 "hi??", RPAD('hi', 1, '??') 返回 "h"。 | | FROM_BASE64(string) | 返回字符串 string1 的 base64 解码的结果；如果字符串为 NULL，则返回 NULL。 例如 FROM_BASE64('aGVsbG8gd29ybGQ=') 返回 "hello world"。 | | TO_BASE64(string) | 返回字符串 string 的 base64 编码的结果；如果字符串为 NULL，则返回 NULL。 例如 TO_BASE64('hello world') 返回 "aGVsbG8gd29ybGQ="。 | | ASCII(string) | 返回字符串 string 第一个字符的数值。如果字符串为 NULL 则返回 NULL。 例如 ascii('abc') 返回 97，ascii(CAST(NULL AS VARCHAR)) 返回 NULL。 | | CHR(integer) | 返回二进制等于 integer 的 ASCII 字符。 如果整数 integer 大于 255，我们先将得到整数对 255 取模数， 并返回模数的 CHR。 如果整数为 NULL，则返回 NULL。 例如 chr(97) 返回 a，chr(353) 返回 a， ascii(CAST(NULL AS VARCHAR)) 返回 NULL。 | | DECODE(binary, string) | 使用提供的字符集（'US-ASCII'，'ISO-8859-1'，'UTF-8'，'UTF-16BE'，'UTF-16LE'，'UTF-16'）解码。 如果任一参数为空，则结果也将为空。 | | ENCODE(string1, string2) | 使用提供的字符集（'US-ASCII'，'ISO-8859-1'，'UTF-8'，'UTF-16BE'，'UTF-16LE'，'UTF-16'）编码。 如果任一参数为空，则结果也将为空。 | | INSTR(string1, string2) | 返回 string2 在 string1 中第一次出现的位置。如果有任一参数为 NULL，则返回 NULL。 | | LEFT(string, integer) | 返回字符串中最左边的长度为 integer 值的字符串。如果 integer 为负，则返回 EMPTY 字符串。如果有任一参数 为 NULL 则返回 NULL。 | | RIGHT(string, integer) | 返回字符串中最右边的长度为 integer 值的字符串。如果 integer 为负，则返回 EMPTY 字符串。如果有任一参数 为 NULL 则返回 NULL。 | | LOCATE(string1, string2\[, integer\]) | 返回 string2 中 string1 在位置 integer 之后第一次出现的位置。未找到返回 0。如果有任一参数为 NULL 则返回 NULL。 | | PARSE_URL(string1, string2\[, string3\]) | 从 URL 返回指定的部分。string2 的有效值包括"HOST"，"PATH"，"QUERY"，"REF"，"PROTOCOL"，"AUTHORITY"，"FILE"和"USERINFO"。 如果有任一参数为 NULL，则返回 NULL。 例如 parse_url(' http://facebook.com/path1/p.php?k1=v1\&k2=v2#Ref1', 'HOST') 返回 'facebook.com'。 还可以通过提供关键词 string3 作为第三个参数来提取 QUERY 中特定键的值。 例如 parse_url('http://facebook.com/path1/p.php?k1=v1\&k2=v2#Ref1', 'QUERY', 'k1') 返回 'v1'。 | | REGEXP(string1, string2) | 如果 string1 的任何（可能为空）子字符串与 Java 正则表达式 string2 匹配，则返回 TRUE，否则返回 FALSE。 如果有任一参数为 NULL，则返回 NULL。 | | REVERSE(string) | 返回反转的字符串。如果字符串为 NULL，则返回 NULL。 | | SPLIT_INDEX(string1, string2, integer1) | 通过分隔符 string2 拆分 string1，返回拆分字符串的第 integer（从零开始）个字符串。如果整数为负，则返回 NULL。 如果有任一参数为 NULL，则返回 NULL。 | | STR_TO_MAP(string1\[, string2, string3\]) | 使用分隔符将 string1 拆分为键值对后返回一个 map。string2 是 pair 分隔符，默认为 ','。string3 是键值分隔符，默认为 '='。 pair 分隔符与键值分隔符均为正则表达式，当使用特殊字符作为分隔符时请提前进行转义，例如 \<(\[{\\\^-=$!\|\]})?\*+.\>。 | | SUBSTR(string\[, integer1\[, integer2\]\]) | 返回字符串的子字符串，从位置 integer1 开始，长度为 integer2（默认到末尾）。 | | JSON_VAL(STRING json_string, STRING json_path) | 从json形式的字符串json_string中提取指定json_path的值。具体函数使用可以参考[JSON_VAL函数使用说明]说明。 说明： 以下规则优先级按照顺序从高到低。 1. 不允许json_string和json_path为NULL 2. json_string格式必须为合法的json串，否则函数返回NULL 3. json_string为空字符串，则函数返回空字符串 4. json_path为空字串或路径不存在，则函数返回NULL | #### JSON_VAL函数使用说明 - 语法 ``` STRING JSON_VAL(STRING json_string, STRING json_path) ``` 表2参数说明 | 参数 | 数据类型 | 说明 | |:---|:---|:---| | json_string | STRING | 需要解析的JSON对象，使用字符串表示。 | | json_path | STRING | 解析JSON的路径表达式，使用字符串表示。 目前path支持如下表达式参考下表[表3]。 | 表3json_path参数支持的表达式 | 表达式 | 说明 | |:---|:---| | $ | 根对象 | | \[\] | 数组下标 | | \* | 数组通配符 | | . | 取子元素 | - 示例 1. 测试输入数据。 测试数据源kafka，具体消息内容参考如下： ``` {"name":"James","age":24,"gender":"male","grade":{"math":95,"science":[80,85],"english":100}} ``` 2. 使用JSON_VAL编写SQL ``` CREATE TABLE kafkaSource ( message string ) WITH ( 'connector' = 'kafka', 'topic-pattern' = '', 'properties.bootstrap.servers' = ':,:', 'properties.group.id' = '', 'scan.startup.mode' = 'latest-offset', 'format' = 'csv', 'csv.field-delimiter' = '\u0001', 'csv.quote-character' = '''' ); CREATE TABLE printSink ( message1 STRING, message2 STRING, message3 STRING, message4 STRING, message5 STRING, message6 STRING ) WITH ( 'connector' = 'print' ); insert into printSink select JSON_VAL(message,''), JSON_VAL(message,'$.name'), JSON_VAL(message,'$.grade.science'), JSON_VAL(message,'$.grade.science[*]'), JSON_VAL(message,'$.grade.science[1]'), JSON_VAL(message,'$.grade.dddd') from kafkaSource; ``` 3. 查看taskmanager的out文件的输出结果 ``` +I[null, James, [80,85], [80,85], 85, null] ```