Spark SQL文本函数及应用

判断是否为null值

isnull()

true/false

判断是否非null值

isnotnull()

true/false

判断null值并替换null值

ifnull(expr1, expr2)

ifnull(null,0)

0

返回第一个非null值的值

coalesce(expr1, expr2, ...)

coalesce(NULL, 1, NULL)

1

返回字符串的长度

length()

length('ABC DE')

6

将英文字符串转为首字母大写

initcap()

initcap('ABC DE')

Abc De

将英文字符串转为小写

lcase()/lower()

lcase('ABC'); lower('ABC')

abc

将英文字符串转为大写

ucase()/upper()

ucase('abc'); upper('abc')

ABC

将字符串反转

reverse()

reverse('abc')

cba

将其他类型转换为字符串

string()

string(123)

123

cast( expr as string)

cast(123 as string)

123

返回第一次匹配到的字符串所在位置

locate(substr, str[, pos])  [, pos]用来指定起始查询位置，可省略。

locate('n','Ann',3)

3

instr(str, substr)

instr('Ann','n')

2

find_in_set(str, str_array) 被查询字符串是以逗号隔开的字符串。

find_in_set('ab','abc,b,ab,c,def')

3

返回正则匹配到的第一个子串

regexp_extract(str, regexp[, idx])

regexp_extract('*A1B2*C3**','[A-Z]+',0)

A

模糊匹配指定模式的字符串，返回 true/false。需结合通配符使用。

like

'A&B'  like '%&%';                   'A&B'  like '&%'

true;              false

模糊匹配指定模式的字符串，返回 true/false。需结合正则表达式使用。

rlike

'PURE&MILD' rlike '[A-Z]+\&[A-Z]+'

true

字符串截取，截取长度[, len]省略时截取指定位置开始的所有字符

substr(str, pos[, len]) ;                substring(str, pos[, len])

substring('123abcABC', 2, 3); substr('Spark SQL', -3)

23a;                              SQL

返回字符串中在第 n 个出现的分隔符之前的子串。n 是负数时，返回从右边开始第 -n 个分隔符到右边所有字符。

substring_index(str, delim, n)

substring_index('a.b.c.d.e', '.', 2);   substring_index('a.b.c.d.e', '.', -2)

a.b;                                 d.e

从左侧开头处截取固定长度字符串

left(str, len)

left('Spark SQL', 3)

Spa

从右侧结尾处截取固定长度字符串

right(str, len)

right('Spark SQL', 3)

SQL

移除字符串开头（左侧）的空格

ltrim(str) ;                    trim(LEADING FROM str)

ltrim('  Spark SQL')

Spark SQL

移除字符串结尾（右侧）的空格

rtrim(str) ;                 trim(TRAILING FROM str)

rtrim('Spark SQL    ')

Spark SQL

移除字符串开头和结尾（左右两侧）的空格

trim(str) ;                          trim(BOTH FROM str)

trim('  Spark SQL    ')

Spark SQL

移除字符串开头和结尾（左右两侧）的指定字符

trim(trimStr FROM str) 移除两侧; trim(LEADING trimStr FROM str) 移除左侧;                    trim(TRAILING trimStr FROM str) 移除右侧

trim('*' from '*AB*C**')

AB*C

替换所有匹配到的字符。[, replace]省略时移除所有匹配到的字符。

replace(str, search[, replace])

replace('ABCabc', 'abc', 'DEF')

ABCDEF

多字符替换。针对input，将from中的每个字符替换为to中相应字符。若from比to字符串长，在from中比to中多出的字符将会被删除。

translate(input, from, to)

translate('AaBbCc', 'abc', '123')； translate('AaBbCc', 'abc', '12')

A1B2C3；                     A1B2C

替换固定位置字符，可指定替换长度

overlay(input, replace, pos[, len])

overlay('Spark SQL' ,'tructured' ,2,4)；overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4)

Structured SQL

正则匹配替换所有匹配到的字符

regexp_replace(str, regexp, rep)

REGEXP_REPLACE('*A1B2*C3**','[\\d\*]','') 把所有数字和*去除

ABC

以单个或多个字符分割字符串, 返回数组。分隔符支持正则表达式，limit控制分割后元素数，省略时代表全部分割

split(str, regex, limit)

split('A1B2C','\\d'); split('A1B2C','\\d',2);                   split('A-B-C','-')

[A, B, C];                           [A, B2C];                        [A, B, C]

字符串拼接

concat

concat('Spark', 'SQL')

SparkSQL

expr1 || expr2

'Spark' || 'SQL'

SparkSQL

用分隔符拼接字符串或数组

concat_ws(sep[, str | array(str)]+)

concat_ws('-', 'Spark', 'SQL')

Spark-SQL

返回字符串重复对应数值次数后的新字符串

repeat(str, n)

repeat('ABC', 2)

ABCABC