主页 / dba / 问题 / 338825
Accepted
user2370668
Asked: 2024-04-20 01:08:07 +0800 CST

根据分隔符从字符串列中提取数据

  • 772

我有一个具有这样价值的专栏。

Declare @Table TABLE (ID int, Val varchar(100))

Insert into @Table
Select 1, 'happy_Summer_Holiday_by_Jan_2024.xlsx'

我想动态提取 Jan 作为月份，2024 年作为年份。

t-sql

1 个回答

  1. Best Answer

    假设月份和年份始终是文件名的最后部分（如单个示例中所示），我会这样做。

    Declare @Table TABLE (ID int, Val varchar(100))

Insert into @Table
Select 1, 'happy_Summer_Holiday_by_Jan_2024.xlsx'

;WITH CTE_Split AS
    (
    SELECT T.ID
        , T.Val 
        , REVERSE(P.[value]) AS StringPart
        , P.ordinal
    FROM @Table AS T
        OUTER APPLY STRING_SPLIT(REVERSE(T.Val), '_', 1) AS P
    )
, CTE_UP AS
    (
    SELECT ID
        , Val
        , LEFT([1], CHARINDEX('.', [1], 0)-1) AS [Year]
        , [2] AS [Month]
    FROM CTE_Split AS S
        PIVOT (MAX(StringPart) FOR ordinal IN ([1], [2])) AS pvt 
    )
SELECT * FROM CTE_Up
    • 1

相关问题