Split One Column into Multiple that uses / as the delimiter
-
I've been researching this for over 3 hours and none of the syntax I've found works. I have a column with data that's been merged from various other tables and they used a / to decipher between each field.
Invoice/14/248/TD C/O BGIS/20009. 1/ 16566/JB App #5
I need to take this and get this which takes seconds in Excel but need it to output like this in SQL. I thought this would be fairly simple?
-
It is. Google for function "dbo.DelimitedSplit8K". It's a function you install once and can use from then on for this.
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".
-
I checked my db and that function is there and created 6 years ago but none of my existing code used it. From what I can see, this one was replaced in SQL 2016 by the STRING_SPLIT but both of these convert the single column into rows and not columns?
-
I found my code that used this function but it splits the single column into multiple rows by the delimiter vs into multiple columns but think I can use it to just grab the one column I need then use that in my main query. Thank you this helped.
-
This is probably the fastest code but a bit long winded
declare @Delimiter char(1) = '/'
;with cte as
(
select 'Invoice/14/248/TD C/O BGIS/20009. 1/ 16566/JB App #5' C
)
select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8
from cte T
cross apply (values (CHARINDEX(@Delimiter,T.C))) AS c1(DelimPos)
cross apply (values (LEFT(T.C,ABS(c1.DelimPos-1)),SUBSTRING(T.C,c1.DelimPos+1,8000))) t1(Col1,RHS)
cross apply (values (CHARINDEX(@Delimiter,t1.RHS))) c2(DelimPos)
cross apply (values (LEFT(t1.RHS,ABS(c2.DelimPos-1)),SUBSTRING(t1.RHS,c2.DelimPos+1,8000))) t2(Col2,RHS)
cross apply (values (CHARINDEX(@Delimiter,t2.RHS))) c3(DelimPos)
cross apply (values (LEFT(t2.RHS,ABS(c3.DelimPos-1)),SUBSTRING(t2.RHS,c3.DelimPos+1,8000))) t3(Col3,RHS)
cross apply (values (CHARINDEX(@Delimiter,t3.RHS))) c4(DelimPos)
cross apply (values (LEFT(t3.RHS,ABS(c4.DelimPos-1)),SUBSTRING(t3.RHS,c4.DelimPos+1,8000))) t4(Col4,RHS)
cross apply (values (CHARINDEX(@Delimiter,t4.RHS))) c5(DelimPos)
cross apply (values (LEFT(t4.RHS,ABS(c5.DelimPos-1)),SUBSTRING(t4.RHS,c5.DelimPos+1,8000))) t5(Col5,RHS)
cross apply (values (CHARINDEX(@Delimiter,t5.RHS))) c6(DelimPos)
cross apply (values (LEFT(t5.RHS,ABS(c6.DelimPos-1)),SUBSTRING(t5.RHS,c6.DelimPos+1,8000))) t6(Col6,RHS)
cross apply (values (CHARINDEX(@Delimiter,t6.RHS))) c7(DelimPos)
cross apply (values (LEFT(t6.RHS,ABS(c7.DelimPos-1)),SUBSTRING(t6.RHS,c7.DelimPos+1,8000))) t7(Col7,Col8) -
in order to split a string like that into individual columns you have to first split into rows then aggregate onto columns
sample code below - function DelimitedSplit8K_LEAD is from Erikur Eiriksson as found here (https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2)
declare @Delimiter char(1) = '/'
;with cte as
(
select 'Invoice/14/248/TD C/O BGIS/20009. 1/ 16566/JB App #5' C
)
select cte.c
, max(case when t.ItemNumber = 1 then t.Item end) as Col1
, max(case when t.ItemNumber = 2 then t.Item end) as Col2
, max(case when t.ItemNumber = 3 then t.Item end) as Col3
, max(case when t.ItemNumber = 4 then t.Item end) as Col4
, max(case when t.ItemNumber = 5 then t.Item end) as Col5
, max(case when t.ItemNumber = 6 then t.Item end) as Col6
, max(case when t.ItemNumber = 7 then t.Item end) as Col7
, max(case when t.ItemNumber = 8 then t.Item end) as Col8
from cte
outer apply DelimitedSplit8K_lead(c, @Delimiter) t
group by cte.c -
This was removed by the editor as SPAM
- Luv SQL wrote:
I've been researching this for over 3 hours and none of the syntax I've found works. I have a column with data that's been merged from various other tables and they used a / to decipher between each field.
Invoice/14/248/TD C/O BGIS/20009. 1/ 16566/JB App #5
I need to take this and get this which takes seconds in Excel but need it to output like this in SQL. I thought this would be fairly simple?
Is the original data in a file?
--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:Luv SQL wrote:
I've been researching this for over 3 hours and none of the syntax I've found works. I have a column with data that's been merged from various other tables and they used a / to decipher between each field.
Invoice/14/248/TD C/O BGIS/20009. 1/ 16566/JB App #5
I need to take this and get this which takes seconds in Excel but need it to output like this in SQL. I thought this would be fairly simple?
Is the original data in a file?
Re-posting the question because it will make life so much simpler for you if it is... Is the original data in a file?
I'll also ask, how many rows of data are there if it's not in a file?
I ask the latter question because, especially if the data is in good shape, it' easier and faster to export the data as it is and then import it using BULK INSERT, especially if it's Minimally Logged. Then you don't have to fart around with re-pivoting, converting datatypes, etc, etc and it can even sequester bad lines of data without stopping good rows from "going in".
--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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply