【Spark SQL】文本函数及业务场景使用

字符串处理方式

1. 基本操作

函数	函数含义	举例	结果
isnull()	判断是否为null值		true/false
isnotnull()	判断是否非null值		true/false
ifnull(expr1, expr2)	判断null值并替换null值	A=null B=1 if(A,B)	1
coalesce(expr1, expr2, …)	返回第一个非null值的值	A=null B=1 C=1 coalesce(A,B,C)	1
length()	返回字符串的长度	length(‘woshi wo’)	8
initcap()	将英文字符串转为首字母大写	initcap(‘woshi wo’)	Woshi Wo
lcase()/lower()	将英文字符串转为小写	lcase(‘ABC’); lower(‘ABC’)	abc
ucase()/upper()	将英文字符串转为大写	ucase(‘abc’); upper(‘abc’)	ABC
reverse()	将字符串反转	reverse(‘123’)	‘321’
string()	将其他类型转换为字符串	string(123)	‘123’
cast( expr as string)	将其他类型转换为字符串	cast(123 as string)	‘123’

2. 字符串查找

函数	函数含义	举例
locate(‘需要查找的字符串’, ‘被查找字符串’,‘查询的起始位置(选填)’)	返回第一次匹配到的字符串所在位置	locate(‘w’,‘qoeqrwe2w123’)=6 locate(‘w’,‘qoeqrwe2w123’,5)=6 locate(‘w’,‘qoeqrwe2w123’,7) =9
instr( ‘被查找字符串’,‘需要查找的字符串’)	返回第一次匹配到的字符串所在位置	instr(‘Wanni’,‘n’)=3
find_in_set(str, str_array) 被查询字符串是以逗号隔开的字符串。	返回第一次匹配到的字符串所在位置	find_in_set(‘ab’,‘abc,b,ab,c,def’)=3
find_in_set(str, str_array) 被查询字符串是以逗号隔开的字符串。	返回第一次匹配到的字符串所在位置	find_in_set(‘ab’,‘abc,b,ab,c,def’)=3
like	模糊匹配指定模式的字符串，返回 true/false。需结合通配符使用	(‘wAn’ like ‘A’ )=true
rlike	模糊匹配指定模式的字符串，返回 true/false。需结合正则表达式使用	(‘ssssqw100ml’ elike ‘\d’ )=false

3. 字符串截取、替换、分割和拼接

用途	函数	举例
字符串截取，截取长度[, len]省略时截取指定位置开始的所有字符	substr(str, pos[, len]) ;	substring(str, pos[, len]) substring(‘123abcABC’, 2, 3)=23a; substr(‘Spark SQL’, -3)=SQL
返回字符串中在第 n 个出现的分隔符之前的子串。n 是负数时，返回从右边开始第 -n 个分隔符到右边所有字符。	substring_index(str, delim, n)	substring_index(‘a.b.c.d.e’, ‘.’, 2)=a.b; substring_index(‘a.b.c.d.e’, ‘.’, -2)= 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 ‘ABC**’)=ABC
替换所有匹配到的字符。[, 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’)=A1B2C3； translate(‘AaBbCc’, ‘abc’, ‘12’)=A1B2C
替换固定位置字符，可指定替换长度	overlay(input, replace, pos[, len])	overlay(‘Spark SQL’ ,‘tructured’ ,2,4)=Structured SQL；overlay(‘Spark SQL’ PLACING ‘tructured’ FROM 2 FOR 4)=Structured SQL
正则匹配替换所有匹配到的字符	regexp_replace(str, regexp, rep)	REGEXP_REPLACE(‘A1B2C3**’,‘[\d*]’,‘’) =‘ABC’
以单个或多个字符分割字符串, 返回数组。分隔符支持正则表达式，limit控制分割后元素数，省略时代表全部分割	split(str, regex, limit) split(‘A1B2C’,‘\d’)=[A, B, C];	split(‘A1B2C’,‘\d’,2)=[A, B2C]; split(‘A-B-C’,‘-’)= [A, B, C]
字符串拼接	concat	concat(‘Spark’, ‘SQL’)=SparkSQL
字符串拼接	expr1 丨丨expr2	Spark’ 丨丨 ‘SQL’=SparkSQL
用分隔符拼接字符串或数组	concat_ws(sep[, str	array(str)]+)
返回字符串重复对应数值次数后的新字符串	repeat(str, n)	repeat(‘ABC’, 2)=ABCABC

业务场景

商品名称里包含了规格，需要解析出规格用于匹配
示例：

商品名称	sku	需要获取的规格
xx清透沐浴露沐浴洗澡女夏500ml	Esjw1k	500ml
xx清透沐浴露沐浴2135洗21澡男夏300ML	Esjw1k	300ml

解决sql：

# LOWER() 转换小写
# REGEXP_SUBSTR(字段, 正则表达式)  查找符合正则表达式的值
select CASEWHEN LOWER(商品名称 ) LIKE '%ml%' THENREGEXP_SUBSTR(LOWER(商品名称 ), '[0-9]+(?=ml)') || 'ml'ELSENULLEND as `需要获取的规格` from table