我需要替换,格式化字符串,使其与键匹配。
——替换符号(),不带空格,——用下划线替换单个空格,——将BAND替换为BD
。x_input =高等教育工作者10级,2级(受薪)x_output = HEW_HIGHER_EDUCATION_WORKER_LEVEL_10_BD_2_SALARIED
我已经用嵌套替换编写了代码,它给出了正确的输出模式
select 'Higher Education Worker Level 10, Band 2 (salaried)' as class_0,
replace(replace(replace(upper('Higher Education Worker Level 10, Band 2 (salaried)'), '(', ''), ')', ''), ' ', '_') as class_1,
replace(class_1, ',', '') as class_2,
replace(class_2, 'Band', 'BD') as class_4
是否有更优雅的方法来做到这一点,我正在阅读snowflake regex模式匹配帮助,但无法找到一个更干净的方法,它太嵌套几个迭代。
如有任何提示,不胜感激。
感谢对于单个字符的替换和删除,您可以使用translate()
,这将大大缩短多个replace()
。
- https://docs.snowflake.com/en/sql-reference/functions/translate.html
查询结果与问题相同,但代码更少:
select 'Higher Education Worker Level 10, Band 2 (salaried)' as class_0,
translate(upper(class_0), ' ()', '_') as class_1,
replace(class_1, ',', '') as class_2,
replace(class_2, 'Band', 'BD') as class_4;
一步:
select replace(translate(upper(class_0), ' (),', '_'), 'Band', 'BD') class_4
from (
select 'Higher Education Worker Level 10, Band 2 (salaried)' class_0
)