【Spark SQL】文本函数及业务场景使用
字符串处理方式
- 基本操作
| 函数 | 函数含义 | 举例 | 结果 |
|---|---|---|---|
| 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’ |
- 字符串查找
| 函数 | 函数含义 | 举例 |
|---|---|---|
| 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 |
- 字符串截取、替换、分割和拼接
| 用途 | 函数 | 举例 |
|---|---|---|
| 字符串截取，截取长度[, 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 CASE
WHEN LOWER(商品名称 ) LIKE '%ml%' THEN
REGEXP_SUBSTR(LOWER(商品名称 ), '[0-9]+(?=ml)') || 'ml'
ELSE
NULL
END as `需要获取的规格` from table
原文地址：https://blog.csdn.net/qq_33407429/article/details/143001203
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！