sql - 从sql中具有多种模式的多个列中提取特定的字符串值

Question

我已经输入了混合模式（列值）的表，我想根据附加的屏幕截图提取输出。请找到我的查询并帮助我。

输入表：

输出表：

我的查询：

WITH _extract_franchise_code_challan_number
AS
(
    Select product_code
    ,ltrim(rtrim(fn1.value)) as value
    from [dbo].product 
    cross apply STRING_SPLIT(franchise_code1,',') fn1 

    union

    Select product_code
    ,ltrim(rtrim(fn2.value)) as value
    from [dbo].product  
    cross apply STRING_SPLIT(franchise_code2,',') fn2 

    union

    Select product_code
    ,ltrim(rtrim(fn3.value)) as value
    from [dbo].product 
    cross apply STRING_SPLIT(franchise_code3,',') fn3 
)
Select product_code
,value as splited_data
,case when ltrim(Substring(value, Charindex(';', value)+1, LEN(value))) like 'CH%' then ltrim(Substring(value, Charindex(';', value)+1, LEN(value)))
      else '' end as extracted_challan_number
,dbo.udf_GetNumeric(Substring(value, 1,Charindex(';', value)-1)) as franchise_code
,CASE WHEN dbo.udf_GetNumeric(Substring(Substring(value, Charindex(';', value)+1, LEN(value)), 1,Charindex('-', Substring(value, Charindex(';', value)+1, LEN(value)))-1)) = '' THEN NULL ELSE dbo.udf_GetNumeric(Substring(Substring(value, Charindex(';', value)+1, LEN(value)), 1,Charindex('-', Substring(value, Charindex(';', value)+1, LEN(value)))-1)) END as challan_from
,CASE WHEN dbo.udf_GetNumeric(Substring(Substring(value, Charindex(';', value)+1, LEN(value)), Charindex('-', Substring(value, Charindex(';', value)+1, LEN(value)))+1, LEN(Substring(value, Charindex(';', value)+1, LEN(value))))) = '' THEN NULL ELSE dbo.udf_GetNumeric(Substring(Substring(value, Charindex(';', value)+1, LEN(value)), Charindex('-', Substring(value, Charindex(';', value)+1, LEN(value)))+1, LEN(Substring(value, Charindex(';', value)+1, LEN(value))))) END as challan_to
from _extract_franchise_code_challan_number

WHERE value <>''

UDF 功能：

CREATE FUNCTION [dbo].[udf_GetNumeric]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END

源数据：

product_code | franchise_code1 | franchise_code2 | franchise_code3

---

32552  FC 19; CH 443358-, FC 22; CH 439031-, FC 25; CH 439031-,
FC 19; CH 443358-, FC 22; CH 439031-, FC 25; CH 439031-,NULL

---

32552 FC 35 FC 25; CH 51424-, FC 35; CH 55158-, FC 70; CH 51424- FC 21; CH 221073-, FC 22; CH 221090-

---

32552 RC10x51,42 FC 22; CH 171902-27000, FC 19; CH -27000, FC 25; CH -27000 NULL

标签： sqlsql-server

Answer 1

您可以使用以下查询

WITH _extract_franchise_code_challan_number
AS
(
Select product_code
    ,ltrim(rtrim(fn1.value)) as value
    from T16
    cross apply STRING_SPLIT(franchise_code1,',') fn1 

    union

    Select product_code
    ,ltrim(rtrim(fn2.value)) as value
    from T16 
    cross apply STRING_SPLIT(franchise_code2,',') fn2 

    union

    Select product_code
    ,ltrim(rtrim(fn3.value)) as value
    from T16
    cross apply STRING_SPLIT(franchise_code3,',') fn3)
select product_code,
       franchise_code,
       case when challan_from = '' then NULL else challan_from end as challan_from,
       case when challan_to = '' then NULL else challan_to end as challan_to
from
(Select 
    product_code,
    Substring(value,4,2) as franchise_code,
    Replace(Substring(Substring(value,CHARINDEX(';',value) + 1,LEN(value)),0,CHARINDEX('-',Substring(value,CHARINDEX(';',value) + 1,LEN(value)))),'CH ','') as challan_from,
    Replace(Substring(Substring(value,CHARINDEX(';',value) + 1,LEN(value)),CHARINDEX('-',Substring(value,CHARINDEX(';',value) + 1,LEN(value))) + 1,LEN(value)),'FC ','') as challan_to
from _extract_franchise_code_challan_number
WHERE value <> '' AND CHARINDEX('-',Substring(value,CHARINDEX(';',value) + 1,LEN(value))) > 0) t

dbfiddle中的演示