观远 BI

Spark SQL文本函数及应用

创建于 2022-11-01 / 最近更新于 2024-01-04 / 6583
字体: [默认] [大] [更大]

1. 基本字符串操作

用途

函数

举例

结果

判断是否为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

2. 字符串查找

用途

函数

举例

结果

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

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

3. 字符串截取

用途

函数

举例

结果

字符串截取,截取长度[, 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

4. 字符串替换

用途

函数

举例

结果

替换所有匹配到的字符。[, 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

5. 字符串分割和拼接

用途

函数

举例

结果

以单个或多个字符分割字符串, 返回数组。分隔符支持正则表达式,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

应用案例:

【需求】文本字段“Sprint”,需要分别提取出:

①中间部分“排期”,由数字和字母组成的6至8位字符串;

②括号里的内容“测试版本”;

③基于“测试版本”推算出“正式版本”;

【逻辑】最后一个小数点后数字为小版本号,为0时前面部分即为正式版本号,不为0时中间数字加1。

【最终效果】

image.png

①"排期"实现方式(任选其一):

1. substr([Sprint],8,8)
2. left(replace([Sprint],'GUANDATA'),8)
3. regexp_extract([Sprint], '(\\d{4,6}\\w{2})', 1)
4. element_at(flatten(sentences([Sprint])),2)

②提取括号里内容“测试版本”实现方式(任选其一):

1. regexp_extract([Sprint], '(\\d\\.\\d{1,2}\\.\\d)', 1)
2. case when instr([Sprint],'(')>0 then replace(substr([Sprint],instr([Sprint],'(')+1),')') end
3. case when [Sprint] like '%(%' then substring_index(translate([Sprint],'()','-'),'-',-1) end
4. element_at(flatten(sentences([Sprint])),3)

③基于“测试版本”推算出“正式版本”实现方式:

case when right([测试版本],1)=0 then substring_index([测试版本],'.',2)
else concat(left([测试版本],2),int(substr(substring_index([测试版本],'.',2),3)+1))
end


16 人点赞过