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
4条答案
按热度按时间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.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.
hzbexzde2#
db<>fiddle
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
Output
jpfvwuh44#
Just another option using
string_split()
andstring_agg()
Example
Results