如何检查字符串是否以日期结尾并剥离结果？ - SQL

发布于 2025-01-20 15:16:43 字数 524 浏览 1 评论 0原文

我正在尝试检查字符串是否以年份结尾。

输入：

file_paths
wowefnowinf/wefionwe/wefowoi/2012-02-03
weofnweofn/weoew/2022-03-04
ewpfowe/ewopfew

所需的输出：

wowefnowinf/wefionwe/wefowoi/
weofnweofn/weoew/
ewpfowe/ewopfew

我首先无法检测到字符串本身以日期格式结尾。这是我的查询：

SELECT CASE WHEN 'wowefnowinf/wefionwe/wefowoi/2012-02-03' LIKE '%/####\-##\-##'
 THEN TRUE
 ELSE FALSE END AS result
FROM my_table;

我应该为此得到 true，但我的查询返回 false。任何帮助将不胜感激。

原文

I'm trying to check whether or not a string ends in a year.

Input:

file_paths
wowefnowinf/wefionwe/wefowoi/2012-02-03
weofnweofn/weoew/2022-03-04
ewpfowe/ewopfew

Desired Output:

wowefnowinf/wefionwe/wefowoi/
weofnweofn/weoew/
ewpfowe/ewopfew

I'm having trouble first detecting that the strings themselves end in a date-format. This is my query:

SELECT CASE WHEN 'wowefnowinf/wefionwe/wefowoi/2012-02-03' LIKE '%/####\-##\-##'
 THEN TRUE
 ELSE FALSE END AS result
FROM my_table;

I should be getting true for this, but my query returns false. Any help would be appreciated.

分享到QQ

分享到微博

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

发布评论

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

画中仙 2025-01-27 15:16:43

在雪花中，您可以使用Regexp_like和split_part：

with dummy as (
    select 'wowefnowinf/wefionwe/wefowoi/2012-02-03' as file_path
    union all
    select 'weofnweofn/weoew/2022-03-04'
    union all
    select 'ewpfowe/ewopfew'
)
select 
    file_path,
    split_part(file_path, '/', -1) as splitted_file_path,
    regexp_like(splitted_file_path, '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]') as ends_with_date,
    iff(ends_with_date, trim(file_path, splitted_file_path), file_path) as trimmed_file_path
from dummy;

输出：

In Snowflake you can make use of regexp_like and split_part:

with dummy as (
    select 'wowefnowinf/wefionwe/wefowoi/2012-02-03' as file_path
    union all
    select 'weofnweofn/weoew/2022-03-04'
    union all
    select 'ewpfowe/ewopfew'
)
select 
    file_path,
    split_part(file_path, '/', -1) as splitted_file_path,
    regexp_like(splitted_file_path, '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]') as ends_with_date,
    iff(ends_with_date, trim(file_path, splitted_file_path), file_path) as trimmed_file_path
from dummy;

Output: