SQL Server Create a query that uses string_split

xoshrz7s 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(157)

Scenario: Create a query that will output the LegID and the CarriCode where in the MCode should matched on the 3rd table. I am using string_split to individually check if the MCode is existing in the 3rd table

Sample table and data can be found here: http://sqlfiddle.com/#!18/f8bd9f

The expected output is this:

LegID, CarriCode
1        AB
3        AD

since CarriCode AB and AD has an MCode of DJM_3R, DJM_1R respectively wherein their MCode is existing in dbo.EFTest table.

My sample query looks like this:

SELECT L.LegID, L.CarriCode
INTO #test
FROM dbo.Leg L 
INNER JOIN dbo.[Carri] C 
    ON L.CarriCode = C.CarriCode 
    AND CType = 'H'
WHERE MCode IN (select MCode FROM string_split(MCode, ','))

Any ideas how can I output the correct data?

in case the sql fiddle link didnt work, I attached the DML , ddl

--table1
CREATE TABLE [dbo].[Leg](
[LegID] [bigint] NOT NULL,
[CarriCode] [varchar](3) NOT NULL,
[FNumber] [char](4) NOT NULL,
[MCode] [varchar](8) NOT NULL)

INSERT INTO [dbo].[Leg]
(
    [LegID],
    [CarriCode],
    [FNumber],
    [MCode]
)
VALUES
(1, 'AB', 2222, 'DJM_3R'),
(2, 'AC', 1222, 'DJM_5R'),
(3, 'AD', 1232, 'DJM_1R')

 -- table2
CREATE TABLE [dbo].[Carri](
    [CarriCode] [varchar](3) NOT NULL,
    [CName] varchar(50) NOT NULL,
    [CType] [char](1) NOT NULL
) 

INSERT INTO [dbo].[Carri]
     ([CarriCode],
    [CName],
    [CType])
VALUES
    ( 'AB','AB Test', 'H'),
    ( 'AC','AC Test', 'L'),
    ( 'AD','AD Test', 'H')

-- table 3
CREATE TABLE [dbo].[EFTest](
    [FCode] [varchar](20) NOT NULL,
    [Desc] [varchar](max) NOT NULL,
    [MCode] [varchar](max) NOT NULL,
)

INSERT INTO  [dbo].[EFTest]
VALUES ('CODE', 'test', 'DJM_3R, DJM_2R, DJM_1R, AAABBB')

sql-server

来源：https://stackoverflow.com/questions/75615956/create-a-query-that-uses-string-split

1条答案

按热度按时间

gstyhher1#

To account for the spaces after each comma in eftest.mcode you need to trim the result of string_split. Like this:

SELECT L.LegID, L.CarriCode, l.mcode
FROM dbo.Leg L 
INNER JOIN dbo.[Carri] C WITH(NOLOCK) 
    ON L.CarriCode = C.CarriCode 
    AND CType = 'H'
where l.mcode IN (
                select trim(value)
                from eftest
                cross apply string_split(mcode,',')
                 )

The result:

LegID   CarriCode   mcode
1       AB          DJM_3R
3       AD          DJM_1R

(the third column can be omitted if unwanted in the result, it was helpful to debug the problem)

see: http://sqlfiddle.com/#!18/3d5805/1

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Create a query that uses string_split

1条答案

相关问题

热门标签

最新问答