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