March 7, 2022 at 11:24 am
Hi all,
Could you please help me to parse string? The format of the string below:
Data:A:B:C:20:0:0
I need to fetch number 20 from this string. So,
1. it should always start with Data
2. It should be always after fourth colon
3. The length of this can be different. That is, it can be 20 or 20.05. Can this be limited to the fifth colon?
Thanks
March 7, 2022 at 11:57 am
One method would be to use DelimitedSplit8K_LEAD , which returns the ordinal position of the string, and then you can easily get the Nth value:
SELECT *
FROM Utility.dbo.DelimitedSplit8K_LEAD('Data:A:B:C:20:0:0',':')
WHERE ItemNumber = 5;
If you were on Azure SQL Database, you could also use STRING_SPLIT and include the optional ordinal parameter:
SELECT *
FROM STRING_SPLIT('Data:A:B:C:20:0:0',':',1)
WHERE ordinal = 5;
Hopefully this parameter will be added to SQL Server 2022 as well.
If, for some reason, you're against functions, you could take the definition DelimitedSplit8k_LEAD and inline it into your query. Otherwise you could nest some CHARINDEX functions, but it's... Well, pretty ugly.
SELECT SUBSTRING(V.YourString,CI4.CI+1, CI5.CI - CI4.CI - 1)
FROM (VALUES('Data:A:B:C:20:0:0'))V(YourString)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString)))CI1(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI1.CI+1)))CI2(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI2.CI+1)))CI3(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI3.CI+1)))CI4(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI4.CI+1)))CI5(CI);
This solution also assumes that there is always another delimiter after the value you want.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 7, 2022 at 3:05 pm
...
SELECT SUBSTRING(V.YourString,CI4.CI+1, CI5.CI - CI4.CI - 1)
FROM (VALUES('Data:A:B:C:20:0:0'))V(YourString)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString)))CI1(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI1.CI+1)))CI2(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI2.CI+1)))CI3(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI3.CI+1)))CI4(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI4.CI+1)))CI5(CI);This solution also assumes that there is always another delimiter after the value you want.
It's easy enough to eliminate that particular worry by adding a trailing delim yourself, just to make sure there is one:
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString+':',CI4.CI+1)))CI5(CI);
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2022 at 5:48 pm
Just a quick question, what are the variations of the values in the first four columns?
😎
March 7, 2022 at 8:28 pm
Thom A wrote:...
SELECT SUBSTRING(V.YourString,CI4.CI+1, CI5.CI - CI4.CI - 1)
FROM (VALUES('Data:A:B:C:20:0:0'))V(YourString)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString)))CI1(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI1.CI+1)))CI2(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI2.CI+1)))CI3(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI3.CI+1)))CI4(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI4.CI+1)))CI5(CI);This solution also assumes that there is always another delimiter after the value you want.
It's easy enough to eliminate that particular worry by adding a trailing delim yourself, just to make sure there is one:
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString+':',CI4.CI+1)))CI5(CI);
Rather - I would do that in the first VALUES statement:
FROM (VALUES(concat('Data:A:B:C:20:0:0', replicate(':', 6)))) V(YourString)And I would make sure there is always the max number + 1 - that way a string that has 0 delimiters will still be parsed correctly. This also allows you to parse out the last element using that last delimiter instead of using some arbitrary end length for the substring or the total length of the string.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 9, 2022 at 6:15 am
Multiple CROSS APPLYs do add a tiny bit of overhead and, in this case, a fair bit of complexity. We can simplify Thom's good code a bit by cascading the CHARINDEX() function in a single VALUES() Table Valued Constructor (AKA Derived Table).
Also, I assume that you actually want to do this to a column in the table. In the following, change "Your Table" to the name of your table and "SomeString" with the name of the column you're trying to do all this with.
SELECT D5 = SUBSTRING(SomeString,ca.P4,CHARINDEX(':',SomeString,ca.P4)-ca.P4)
FROM dbo.YourTable
CROSS APPLY (VALUES
(
CHARINDEX(':',SomeString+':'
,CHARINDEX(':',SomeString
,CHARINDEX(':',SomeString
,CHARINDEX(':',SomeString)+1)+1)+1)+1
)
)ca(P4)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2022 at 4:55 pm
Hi Thom,
Unfortunately, I have too old version - SQL Server 2008 🙂
March 10, 2022 at 5:02 pm
Hi Thom,
Unfortunately, I have too old version - SQL Server 2008 🙂
Why did you post in the SQL Server 2016 forum then? 2008 has been completely unsupported since mid 2020; you really should have upgraded by now or at least finished planning your upgrade path and be about to be deploying it.
If you're using completely unsupported software, then the CHARINDEX method will still work, or you could use DelimitedSplit8k instead (which DelimitedSplit8k_LEAD is based on), and is linked to in the article.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 11, 2022 at 1:05 am
Thank you for the feedback, Christi.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply