September 18, 2013 at 5:09 am
I have string of format with two delimiters ‘|’ pipe and ‘,’ comma
'1,100,12345|2,345,433|3,23423,123|4,33,55'
And have to insert into table columns as below
seq invoiceamount
110012345
2345433
323423123
43355
Please help
Thanks & Regards,
Prathibha
September 18, 2013 at 5:15 am
With the help of SSIS you can achieve this easily
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 18, 2013 at 5:18 am
I don't want to use SSIS.
So far I could get the first split
September 18, 2013 at 5:27 am
ok, you can use DelimitedSplit8K function to split the string by Jeff Modem
you can follow this link
http://www.sqlservercentral.com/articles/Tally+Table/72993/
declare @varchar table
( Name varchar(100)
)
INSERT INTO @varchar values ( '1,100,12345|2,345,433|3,23423,123|4,33,55')
SELECT ITEM
FROM @varchar
CROSS APPLY
dbo.[DelimitedSplit8K]('1,100,12345|2,345,433|3,23423,123|4,33,55','|')
Output:
ITEM
1,100,12345
2,345,433
3,23423,123
4,33,55
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 18, 2013 at 7:11 am
Could implement. Any easier way??
declare @ClaimSequence varchar(2000), @count int, @counter int, @tpin varchar(8000)
SET @ClaimSequence ='101, 1, 300,333|101,2, 30, 400'
CREATE TABLE #temp(id int not null identity(1,1) , sequence varchar(100))
CREATE TABLE #temp2(id int not null identity(1,1) , sequence2 varchar(100))
CREATE TABLE #temp3(id int not null identity(1,1) ,ClaimId int,sequenceid int, invoicenumber int, amount int)
declare @claimid int, @seqno int, @invoiceno int, @amount int
INSERT INTO #temp(sequence)SELECT * from [fn_Split](@ClaimSequence,'|')
--select * from #temp
SET @count = @@rowcount
SET @counter = 1
WHILE @counter <= @count
BEGIN
SELECT @tpin = sequence from #temp where id=@counter
--select @tpin
INSERT INTO #temp2(sequence2) SELECT * from [fn_Split](@tpin,',')
select @claimid =sequence2 from #temp2 where id=1
select @seqno =sequence2 from #temp2 where id=2
select @invoiceno =sequence2 from #temp2 where id=3
select @amount =sequence2 from #temp2 where id=4
--select * from #temp2
INSERT INTO #temp3(ClaimId,sequenceid, invoicenumber,amount)
values(@claimid,@seqno,@invoiceno,@amount)
SET @counter = @counter + 1
truncate table #temp2
END
select * from #temp3
DROP TABLE #temp
DROP TABLE #temp2
DROP TABLE #temp3
September 18, 2013 at 9:06 am
Prathibha.m (9/18/2013)
Could implement. Any easier way??declare @ClaimSequence varchar(2000), @count int, @counter int, @tpin varchar(8000)
SET @ClaimSequence ='101, 1, 300,333|101,2, 30, 400'
CREATE TABLE #temp(id int not null identity(1,1) , sequence varchar(100))
CREATE TABLE #temp2(id int not null identity(1,1) , sequence2 varchar(100))
CREATE TABLE #temp3(id int not null identity(1,1) ,ClaimId int,sequenceid int, invoicenumber int, amount int)
declare @claimid int, @seqno int, @invoiceno int, @amount int
INSERT INTO #temp(sequence)SELECT * from [fn_Split](@ClaimSequence,'|')
--select * from #temp
SET @count = @@rowcount
SET @counter = 1
WHILE @counter <= @count
BEGIN
SELECT @tpin = sequence from #temp where id=@counter
--select @tpin
INSERT INTO #temp2(sequence2) SELECT * from [fn_Split](@tpin,',')
select @claimid =sequence2 from #temp2 where id=1
select @seqno =sequence2 from #temp2 where id=2
select @invoiceno =sequence2 from #temp2 where id=3
select @amount =sequence2 from #temp2 where id=4
--select * from #temp2
INSERT INTO #temp3(ClaimId,sequenceid, invoicenumber,amount)
values(@claimid,@seqno,@invoiceno,@amount)
SET @counter = @counter + 1
truncate table #temp2
END
select * from #temp3
DROP TABLE #temp
DROP TABLE #temp2
DROP TABLE #temp3
There is no need for looping here at all. You just need to understand and leverage the power of the DelimitedSplit8K function.
declare @SomeString varchar(100) = '101, 1, 300,333|101,2, 30, 400';
CREATE TABLE #temp3
(
id int not null identity(1,1),
ClaimId int,
sequenceid int,
invoicenumber int,
amount int
);
INSERT #temp3
select
MAX(case when x.ItemNumber = 1 then x.Item end) as ClaimID,
MAX(case when x.ItemNumber = 2 then x.Item end) as Seq,
MAX(case when x.ItemNumber = 3 then x.Item end) as Invoice,
MAX(case when x.ItemNumber = 4 then x.Item end) as Amount
from dbo.DelimitedSplit8K(@SomeString, '|') fs
cross apply dbo.DelimitedSplit8K(fs.Item, ',') x
group by fs.ItemNumber;
select * from #temp3
drop table #temp3
_______________________________________________________________
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy