SQL Server Get all the characters after the 3rd hypen (-)

tkqqtvp1  于 2023-11-16  发布在  其他
关注(0)|答案(4)|浏览(115)

I have column TrackNo with values like

8070444981-010023-013123-INBBTC-C
601724-072923-078923-INAAAX-B

I need to get the values from the 3rd hypen example

-INBBTC-C
-INAAAX-B

I tried substring,charindex but it is not working for both the scenarios

select substring( Trackno,
charindex('-', TrackNo , (charindex('-', TrackNo , 1))
+charindex('-', TrackNo, (charindex('-', TrackNo , 1))+1)),20) 
from table
ncgqoxb0

ncgqoxb01#

If you have SQL 2022, you can use string_split with the optional ordinal argument, and grab whichever pieces you want (after splitting on - ).

Barring that, you can brute force the piece with nested charindex calls, like you've done. You just need to make sure you're offsetting each chunk correctly so you're not slicing in the wrong place.

;with trackingNumbers (TrackNo) as
(
    select '8070444981-010023-013123-INBBTC-C'
    union all select '601724-072923-078923-INAAAX-B'
), b as
(
    select
        TrackNo,
        FirstIndex = charindex('-', TrackNo),
        SecondIndex = charindex
            (
                '-', 
                TrackNo, 
                charindex
                (
                    '-', 
                    TrackNo
                ) + 1
            ),
        ThirdIndex = charindex
            (
                '-', 
                TrackNo, 
                charindex
                (
                    '-', 
                    TrackNo, 
                    charindex
                    (
                        '-', 
                        TrackNo
                    ) + 1
                ) + 1
            )
    from trackingNumbers
)
select *,
LastTwo = substring(TrackNo, ThirdIndex + 1, 8000)
from b

There are other ways you can do it by replacing various values in your string to coax it into the form of JSON or XML data if you want, but I'll avoid those in the interest of addressing your original attempted approach.

EDIT Just as a bonus, here's how you could do it with successive cross applies, if you want to go that route.

;with trackingNumbers (TrackNo) as
(
    select '8070444981-010023-013123-INBBTC-C'
    union all select '601724-072923-078923-INAAAX-B'
)
select 
    LastTwo = substring(TrackNo, d.ThirdIndex + 1, 8000),
    FirstIndex,
    SecondIndex,
    ThirdIndex
from trackingNumbers a
cross apply
(
    select FirstIndex = charindex('-', TrackNo)
) b
cross apply
(
    select SecondIndex = charindex('-', TrackNo, b.FirstIndex + 1)
) c
cross apply
(
    select ThirdIndex = charindex('-', TrackNo, c.SecondIndex + 1)
) d
hzbexzde

hzbexzde2#

with [table] (Trackno) as (
select '8070444981-010023-013123-INBBTC-C' union
select '601724-072923-078923-INAAAX-B')

select substring(Trackno,
            0 + charIndex('-', TrackNo,
            1 + charIndex('-', TrackNo,
            2 + charIndex('-', Trackno)))
            , 20) as [Result]
from [table]

db<>fiddle

oiopk7p5

oiopk7p53#

Please try the following solution based on XML and XQuery.

The XPath predicate /root/r[position() ge 4] is doing the job for your scenario:
Get all the characters after the 3rd hypen (-)

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Trackno VARCHAR(100));  
INSERT INTO @tbl (Trackno) VALUES 
('8070444981-010023-013123-INBBTC-C'),
('601724-072923-078923-INAAAX-B');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '-';

SELECT t.* 
    , REPLACE(c.query('data(/root/r[position() ge 4])')
        .value('text()[1]', 'VARCHAR(100)'), SPACE(1), @separator) AS result
FROM @tbl AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(Trackno, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c);

Output

IDTracknoresult
18070444981-010023-013123-INBBTC-CINBBTC-C
2601724-072923-078923-INAAAX-BINAAAX-B
jpfvwuh4

jpfvwuh44#

Just another option using string_split() and string_agg()

Example

Declare @YourTable Table ([SomeCol] varchar(50))  Insert Into @YourTable Values 
 ('8070444981-010023-013123-INBBTC-C')
,('601724-072923-078923-INAAAX-B')
 
Select *
from @YourTable
Cross Apply ( Select NewVal =string_agg(value,'-')  within group (order by ordinal)
                 From string_split(SomeCol,'-',1)
                 Where ordinal>3
                 ) B

Results

SomeCol                             NewVal
8070444981-010023-013123-INBBTC-C   INBBTC-C
601724-072923-078923-INAAAX-B       INAAAX-B

相关问题