Snowflake SQL喜欢逃生，下划线和一个符号的通配符

发布于 2025-02-13 18:35:51 字数 585 浏览 0 评论 0原文

我需要从长期代码中返回一个语言代码substring，这些代码在第二个下强调之后特别是3个字母。表中的代码是这样的：

SA_ROWERP0129_ITA_GECP_AUD_ENG_QUATRO
SA_COWER0123_ITA_LECP_AUD_SPA_QUATRO
SA_ZAEP0127_WCPE_2_AUD_ENG_QUATRO

所以我需要返回第一行（不应该返回第三行）

我正在尝试使用的4个字母WCPE），而6个升压和6次升压：

CASE WHEN code LIKE '%^_%^_%^_%^_%^_%^_QUATRO' ESCAPE '^' --this affects all 3

所以我尝试添加“未消除”下强调。三个字母：

CASE WHEN code LIKE '%^____^_%^_%^_%^_%^_QUATRO' ESCAPE '^' --this did not work

如何将下划线（1个符号的通配符）与逃脱的下划线（实际下划线符号）相结合以仅影响代码1和2？

我应该将rlike与某些正则表达式使用吗？

原文

I need to return a language code substring from long codes which have specifically 3 letters after second underscore.
The codes in the table are like these:

SA_ROWERP0129_ITA_GECP_AUD_ENG_QUATRO
SA_COWER0123_ITA_LECP_AUD_SPA_QUATRO
SA_ZAEP0127_WCPE_2_AUD_ENG_QUATRO

So I need to return 1st and 2nd rows (Third should not be returned it has 4 letters WCPE)

I was trying LIKE and 6 underscores with ESCAPE:

CASE WHEN code LIKE '%^_%^_%^_%^_%^_%^_QUATRO' ESCAPE '^' --this affects all 3

So I tried adding "unescaped" underscores for the three letters:

CASE WHEN code LIKE '%^____^_%^_%^_%^_%^_QUATRO' ESCAPE '^' --this did not work

How can I combine underscores (wildcards for 1 symbol) with escaped underscores (actual underscore symbol) to affect only codes 1 and 2?

Should I use RLIKE with some regex instead?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

↘人皮目录ツ 2025-02-20 18:35:51

您可以使用RegeXP函数或拆分函数来抓住第三组字符并检查其长度：

create or replace table T1(CD string);

insert into T1 (CD) values 
('SA_ROWERP0129_ITA_GECP_AUD_ENG_QUATRO'),
('SA_COWER0123_ITA_LECP_AUD_SPA_QUATRO'),
('SA_ZAEP0127_WCPE_2_AUD_ENG_QUATRO');

select *, split_part(CD, '_', 3) as LANGUAGE_CODE
from T1 where len(LANGUAGE_CODE) = 3;

You can use regexp functions, or the split function to grab the third group of characters and check its length:

create or replace table T1(CD string);

insert into T1 (CD) values 
('SA_ROWERP0129_ITA_GECP_AUD_ENG_QUATRO'),
('SA_COWER0123_ITA_LECP_AUD_SPA_QUATRO'),
('SA_ZAEP0127_WCPE_2_AUD_ENG_QUATRO');

select *, split_part(CD, '_', 3) as LANGUAGE_CODE
from T1 where len(LANGUAGE_CODE) = 3;

回复收藏 0 原文

~没有更多了~