January 29, 2024 at 5:02 pm
I realized PARSENAME has a limit of 4-parts for splitting a string with a delim.
What's the alternative for more than 4 parts?
e.g. tag = 'one-two-three-four-five-six'
I want "tag" to be split in 6 parts in a SELECT query.
Thanks,
V
January 29, 2024 at 6:42 pm
DelimitedSplit8K (assuming the column is varchar(8000) or fewer bytes)
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".
January 29, 2024 at 7:46 pm
Scott,
Is it built in function in SQL 2016 SP3? it doesn't look like it's there.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'DelimitedSplit8K'
Vn
January 29, 2024 at 7:53 pm
No, sorry, it's a custom function.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.DelimitedSplit8K (
@pString varchar(8000),
@pDelimiter char(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS
/*SELECT * FROM dbo.DelimitedSplit8K('ab/c/def/ghijklm/no/prq/////st/u//', '/')*/
RETURN
/*Inline CTE-driven "tally table" produces values from 0 up to 10,000: enough to cover varchar(8000).*/
WITH E1(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Ns(N)),
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), --10E+4 or 10,000 rows max
ctetally(N) AS (/* This provides the "zero base" and limits the number of rows right up front,
for both a performance gain and prevention of accidental "overruns". */
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
), cteStart(N1) AS ( /* This returns N+1 (starting position of each "element" just once for each delimiter). */
SELECT t.N+1
FROM ctetally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
/* Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
SELECT ROW_NUMBER() OVER(ORDER BY s.N1) AS ItemNumber,
SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0) - s.N1,8000)) AS Item
FROM cteStart s;
/*end of func*/
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".
January 29, 2024 at 8:11 pm
Scott,
Your function works but outputs in rows.
I want like this in fact:
declare @tag varchar(100)
set @tag = 'one-two-three-four'
select
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 4)))) as [Post0],
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 3)))) as [Post1],
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 2)))) as [Post3],
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 1)))) as [Post4]
The problem with PARSENAME is that I can't add a 5th column. It can't do 5 parts.
Vn
January 29, 2024 at 8:25 pm
Scott,
Your function works but outputs in rows.
I want like this in fact:
declare @tag varchar(100)
set @tag = 'one-two-three-four'
select
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 4)))) as [Post0],
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 3)))) as [Post1],
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 2)))) as [Post3],
upper(convert(varchar(10),ltrim(PARSENAME(REPLACE(@tag, '-', '.'), 1)))) as [Post4]The problem with PARSENAME is that I can't add a 5th column. It can't do 5 parts.
Vn
The complete explanation for DelimitedSplit8k is here.
Will there always be 5 parts?
Or will it vary?
Will this work?
declare @tag varchar(100)
set @tag = 'one-two-three-four-five'
SELECT
MAX(CASE WHEN ItemNumber = 1 THEN ItemValue END)
,MAX(CASE WHEN ItemNumber = 2 THEN ItemValue END)
,MAX(CASE WHEN ItemNumber = 3 THEN ItemValue END)
,MAX(CASE WHEN ItemNumber = 4 THEN ItemValue END)
,MAX(CASE WHEN ItemNumber = 5 THEN ItemValue END)
FROM dbo.DelimitedSplit8k(@tag, '-')
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 29, 2024 at 8:26 pm
If you can provide actual sample data -- CREATE TABLE and INSERT statement(s) -- I can provide code to give you the results you want.
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".
January 29, 2024 at 9:13 pm
You can use something like this:
SELECT ...
, col_1 = substring(v.Tag, 1, p1.pos - 2)
, col_2 = substring(v.Tag, p1.pos, p2.pos - p1.pos - 1)
, col_3 = substring(v.Tag, p2.pos, p3.pos - p2.pos - 1)
, col_4 = substring(v.Tag, p3.pos, p4.pos - p3.pos - 1)
, col_5 = substring(v.Tag, p4.pos, p5.pos - p4.pos - 1)
, col_6 = substring(v.Tag, p5.pos, p6.pos - p5.pos - 1)
FROM {your table}
CROSS APPLY (VALUES (concat(Tag, replicate('-', 6)))) AS v(Tag) -- ensure we have 6 elements
CROSS APPLY (VALUES (charindex('-', v.Tag, 1) + 1) AS p1(pos)
CROSS APPLY (VALUES (charindex('-', v.Tag, p1.pos) + 1) AS p2(pos)
CROSS APPLY (VALUES (charindex('-', v.Tag, p2.pos) + 1) AS p3(pos)
CROSS APPLY (VALUES (charindex('-', v.Tag, p3.pos) + 1) AS p4(pos)
CROSS APPLY (VALUES (charindex('-', v.Tag, p4.pos) + 1) AS p5(pos)
CROSS APPLY (VALUES (charindex('-', v.Tag, p5.pos) + 1) AS p6(pos)
This would work for any string with *up to* 6 elements. If your strings contain more - but you only need to worry about the first 6 this will work for that scenario too. If you need more than the first 6 elements - then you need to modify the code.
If you need a more dynamic approach - then you would use a string split (as in DelimitedSplit8K) that also returns the ordinal position (STRING_SPLIT in SQL Server 2022 or greater) and then PIVOT/CROSS-TAB the results into individual columns.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply