Need Split delimiter string into columns Solution
-
Hi Jeff ,
I'm using SQL Server 2012. I just need to split the values and bring in a select statement. I don't want to insert them anywhere.
- satishrao wrote:
Hi Jeff ,
I'm using SQL Server 2012. I just need to split the values and bring in a select statement. I don't want to insert them anywhere.
The see the article at the following URL. Read the first part. Then, get the function for doing the splits at the end of the article.
https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:satishrao wrote:
Hi Jeff ,
I'm using SQL Server 2012. I just need to split the values and bring in a select statement. I don't want to insert them anywhere.
The see the article at the following URL. Read the first part. Then, get the function for doing the splits at the end of the article.
https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2
You made this seem urgent by PMing me twice about it. A little feedback at to if it worked for you or not would be nice... at least the polite thing to do.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - satishrao wrote:
Hi Guys ,
I have column with Values like 1111\123132\34342323\1212. I used the above code to split it . But this doesn't work. The code returns blank. Can you please look into this and provide some help if possible ?
What code above? If it is that function with the while loop then you need to keep reading this thread, and my responses to understand that is a dreadful way to split strings. There are so many better options than that. See the link in my signature about how to split strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
You could try this STRING_SPLIT function I'm in the process of writing and give me some feedback.
It's like SQL Server's STRING_SPLIT function but will work with SQL 2012 and higher.
IF OBJECT_ID('[dbo].[STRING_SPLIT]','IF') IS NULL BEGIN
EXEC ('CREATE FUNCTION [dbo].[STRING_SPLIT] () RETURNS TABLE AS RETURN SELECT 1 X')
END
GO
ALTER FUNCTION [dbo].[STRING_SPLIT]
(
@string nvarchar(MAX),
@separator nvarchar(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH X(N) AS (SELECT 'Table1' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C)),
Y(N) AS (SELECT 'Table2' FROM X A1, X A2, X A3, X A4, X A5, X A6, X A7, X A8) , -- Up to 16^8 = 4 billion
T(N) AS (SELECT TOP(ISNULL(LEN(@string),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 N FROM Y),
Delim(Pos) AS (SELECT t.N FROM T WHERE (SUBSTRING(@string, t.N, LEN(@separator+'x')-1) LIKE @separator OR t.N = 0)),
Separated(value) AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator+'x')-1, LEAD(d.Pos,1,2147483647) OVER (ORDER BY (SELECT NULL)) - d.Pos - LEN(@separator))
FROM Delim d
WHERE @string IS NOT NULL)
SELECT s.value
FROM Separated s
WHERE s.value <> @separator
GOSample usage:
SELECT value
FROM dbo.STRING_SPLIT('1111\123132\34342323\1212', '\'); -
Sorry , I was in a hurry and looking out for solutions and couldn't respond immediately.
Here is what I'm trying to do . There is a table called Locations and it has a column called Hierarchies.
Hierarchies values are something like this - 2002\23323\132242\343443333 - ParentLoc\Child1Loc\Child2Loc\Child3Loc. A parent can have n number of Child nodes.
I'm trying to split them into different columns Par1,Child1,Child2, Child3 in the select statement so that I can use them in a visualization tool for drilldown purpose.
I did create the function , but cant pass hardcoded values , it needs to work dynamically by taking the values from the said column and split. Thanks for all your inputs so far and looking forward to hear back from you.
- satishrao wrote:
Sorry , I was in a hurry and looking out for solutions and couldn't respond immediately.
Here is what I'm trying to do . There is a table called Locations and it has a column called Hierarchies.
Hierarchies values are something like this - 2002\23323\132242\343443333 - ParentLoc\Child1Loc\Child2Loc\Child3Loc. A parent can have n number of Child nodes.
I'm trying to split them into different columns Par1,Child1,Child2, Child3 in the select statement so that I can use them in a visualization tool for drilldown purpose.
I did create the function , but cant pass hardcoded values , it needs to work dynamically by taking the values from the said column and split. Thanks for all your inputs so far and looking forward to hear back from you.
Still not sure what function you are talking about. And just because an example using a function is passing in hard coded values doesn't mean you can't use a column. See https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Crikey, this is a blast from the past. I wouldn't write it like that now!
- Jonathan AC Roberts wrote:
You could try this STRING_SPLIT function I'm in the process of writing and give me some feedback.
It's like SQL Server's STRING_SPLIT function but will work with SQL 2012 and higher.
IF OBJECT_ID('[dbo].[STRING_SPLIT]','IF') IS NULL BEGIN
EXEC ('CREATE FUNCTION [dbo].[STRING_SPLIT] () RETURNS TABLE AS RETURN SELECT 1 X')
END
GO
ALTER FUNCTION [dbo].[STRING_SPLIT]
(
@string nvarchar(MAX),
@separator nvarchar(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH X(N) AS (SELECT 'Table1' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C)),
Y(N) AS (SELECT 'Table2' FROM X A1, X A2, X A3, X A4, X A5, X A6, X A7, X A8) , -- Up to 16^8 = 4 billion
T(N) AS (SELECT TOP(ISNULL(LEN(@string),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 N FROM Y),
Delim(Pos) AS (SELECT t.N FROM T WHERE (SUBSTRING(@string, t.N, LEN(@separator+'x')-1) LIKE @separator OR t.N = 0)),
Separated(value) AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator+'x')-1, LEAD(d.Pos,1,2147483647) OVER (ORDER BY (SELECT NULL)) - d.Pos - LEN(@separator))
FROM Delim d
WHERE @string IS NOT NULL)
SELECT s.value
FROM Separated s
WHERE s.value <> @separator
GOSample usage:
SELECT value
FROM dbo.STRING_SPLIT('1111\123132\34342323\1212', '\');That's pretty interesting. Looks somewhat similar to Eirikur's but a few differences. The one thing I am not a fan of is that it has the same major miss that the MS STRING_SPLIT function has. It doesn't return the ordinal position of the value within the delimited string. This is so helpful in so many situations.
Have you tried stacking this up next to either DelimitedSplit8K or DelimitedSplit8K_LEAD for performance?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ - Sean Lange wrote:
That's pretty interesting. Looks somewhat similar to Eirikur's but a few differences. The one thing I am not a fan of is that it has the same major miss that the MS STRING_SPLIT function has. It doesn't return the ordinal position of the value within the delimited string. This is so helpful in so many situations.
Have you tried stacking this up next to either DelimitedSplit8K or DelimitedSplit8K_LEAD for performance?
I just wrote it as I thought it would be useful to convert code that ran on SQL 2016 using Microsoft's STRING_SPLIT function to run on 2012+. Or someone who was using 2012+ and wanted to write code that would run on SQL 2016+ using MS's STRING_SPLIT with few changes needed. That's the reason it doesn't return any ordinal position. It also allows a delimiter longer than one character and a string to split longer than 8k chars.
- Jonathan AC Roberts wrote:Sean Lange wrote:
That's pretty interesting. Looks somewhat similar to Eirikur's but a few differences. The one thing I am not a fan of is that it has the same major miss that the MS STRING_SPLIT function has. It doesn't return the ordinal position of the value within the delimited string. This is so helpful in so many situations.
Have you tried stacking this up next to either DelimitedSplit8K or DelimitedSplit8K_LEAD for performance?
I just wrote it as I thought it would be useful to convert code that ran on SQL 2016 using Microsoft's STRING_SPLIT function to run on 2012+. Or someone who was using 2012+ and wanted to write code that would run on SQL 2016+ using MS's STRING_SPLIT with few changes needed. That's the reason it doesn't return any ordinal position. It also allows a delimiter longer than one character and a string to split longer than 8k chars.
All of that is why people are interested in the performance... those are improvements that a lot of people would like.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
All of that is why people are interested in the performance... those are improvements that a lot of people would like.
I found some code someone else has written to test performance of string splitters:
https://sqlperformance.com/2012/07/t-sql-queries/split-strings
set statistics io,time off
if object_id('dbo.strings','U') is not null drop table dbo.strings
CREATE TABLE dbo.strings
(
string_type TINYINT,
string_value NVARCHAR(MAX)
);
CREATE CLUSTERED INDEX IX_string_type_1 ON dbo.strings(string_type);
SET NOCOUNT ON;
GO
CREATE TABLE #x(s NVARCHAR(MAX));
INSERT #x SELECT N'a,id,xyz,abcd,abcde,sa,foo,bar,mort,splunge,bacon,';
GO
INSERT dbo.strings SELECT 1, s FROM #x;
GO 10000
INSERT dbo.strings SELECT 2, REPLICATE(s,10) FROM #x;
GO 1000
INSERT dbo.strings SELECT 3, REPLICATE(s,100) FROM #x;
GO 100
INSERT dbo.strings SELECT 4, REPLICATE(s,1000) FROM #x;
GO 10
INSERT dbo.strings SELECT 5, REPLICATE(s,10000) FROM #x;
GO 5
DROP TABLE #x;
GO
-- then to clean up the trailing comma, since some approaches treat a trailing empty string as a valid element:
UPDATE dbo.strings SET string_value = SUBSTRING(string_value, 1, LEN(string_value)-1) + 'x';Quick check to see what's on the new test data table:
select s.string_type,len(s.string_value) length,count(*) Count
from dbo.strings s
group by s.string_type,len(s.string_value)
order by 1
Then run some tests
SET NOCOUNT OFF;
declare @StringType int = 1
while @StringType < 6 begin
if object_id('tempdb..#1','U') is not null drop table #1
if object_id('tempdb..#2','U') is not null drop table #2
if object_id('tempdb..#3','U') is not null drop table #3
PRINT CONCAT('**************************************************************
','@StringType=',@StringType,'
**************************************************************')
set statistics time on
select a.*
into #1
from dbo.strings s
cross apply dbo.STRING_SPLIT(s.string_value,',') a
where s.string_type=@StringType
select a.*
into #2
from dbo.strings s
cross apply STRING_SPLIT(s.string_value,',') a
where s.string_type=@StringType
select a.Item
into #3
from dbo.strings s
cross apply dbo.DelimitedSplit8K(s.string_value,',') a
where s.string_type=@StringType
set statistics io,time off
SET @StringType +=1
ENDOutput
**************************************************************
@StringType=1 (50 characters)
**************************************************************
dbo.STRING_SPLIT
CPU time = 702 ms, elapsed time = 366 ms.
(110000 rows affected)
STRING_SPLIT
CPU time = 2156 ms, elapsed time = 1150 ms.
(110000 rows affected)
dbo.DelimitedSplit8K
CPU time = 516 ms, elapsed time = 422 ms.
(110000 rows affected)
**************************************************************
@StringType=2 (500 characters)
**************************************************************
dbo.STRING_SPLIT
CPU time = 281 ms, elapsed time = 227 ms.
(110000 rows affected)
STRING_SPLIT
CPU time = 1891 ms, elapsed time = 1109 ms.
(110000 rows affected)
dbo.DelimitedSplit8K
CPU time = 312 ms, elapsed time = 335 ms.
(110000 rows affected)
**************************************************************
@StringType=3 (5,000 characters)
**************************************************************
dbo.STRING_SPLIT
CPU time = 374 ms, elapsed time = 409 ms.
(110000 rows affected)
STRING_SPLIT
CPU time = 594 ms, elapsed time = 774 ms.
(110000 rows affected)
dbo.DelimitedSplit8K
CPU time = 1047 ms, elapsed time = 1130 ms.
(110000 rows affected)
**************************************************************
@StringType=4 (50,000 characters)
**************************************************************
dbo.STRING_SPLIT
CPU time = 704 ms, elapsed time = 717 ms.
(110000 rows affected)
STRING_SPLIT
CPU time = 750 ms, elapsed time = 839 ms.
(110000 rows affected)
dbo.DelimitedSplit8K (over string length limit)
CPU time = 359 ms, elapsed time = 347 ms.
(17610 rows affected)
**************************************************************
@StringType=5 (500,000 characters)
**************************************************************
dbo.STRING_SPLIT
CPU time = 4187 ms, elapsed time = 4892 ms.
(550000 rows affected)
STRING_SPLIT
CPU time = 3579 ms, elapsed time = 4233 ms.
(550000 rows affected)
dbo.DelimitedSplit8K (over string length limit)
CPU time = 312 ms, elapsed time = 342 ms.
(8805 rows affected)
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply