July 5, 2012 at 10:29 pm
HI ALL
I HAVE A STRING WITH DELIMETER
STRING IS 1^2~3^4~5^6
TREAT ^ AS COLUMN SEPARATOR AND ~ AS ROW SEPARATOR
HOW TO HANDLE THIS TYPE OF STRING AND DATA INSERT IN TABLE
WHEN STRING = 1^2~3^4~5^6
THEN INSERT DATA IN TABLE AS AFETR INSERT DATA ON TABLE :-
COL1 COL2
1 2
3 4
5 6
WHEN STRING HAVE N LENGTH
ASSUME STRING = 1^2~3^4~5^6........N
n rows and n columns
THEN INSERT IN TABLE AND SHOW DATA AS
COL1 COL2
1 2
3 4
5 6
.. ..
.. ..
.. ..
.. ..
N N
HOW TO MANAGE THIS PROBLEM
THANKS IN ADVANCE ...
July 5, 2012 at 11:46 pm
anuj12paritosh (7/5/2012)
HI ALLI HAVE A STRING WITH DELIMETER
STRING IS 1^2~3^4~5^6
TREAT ^ AS COLUMN SEPARATOR AND ~ AS ROW SEPARATOR
HOW TO HANDLE THIS TYPE OF STRING AND DATA INSERT IN TABLE
WHEN STRING = 1^2~3^4~5^6
THEN INSERT DATA IN TABLE AS AFETR INSERT DATA ON TABLE :-
COL1 COL2
1 2
3 4
5 6
WHEN STRING HAVE N LENGTH
ASSUME STRING = 1^2~3^4~5^6........N
n rows and n columns
THEN INSERT IN TABLE AND SHOW DATA AS
COL1 COL2
1 2
3 4
5 6
.. ..
.. ..
.. ..
.. ..
N N
HOW TO MANAGE THIS PROBLEM
THANKS IN ADVANCE ...
This should work assuming that the row is having single digit integer in a column as per given pattern.
declare @string varchar(max)
declare @tmpstr varchar(max)
declare @found bit
declare @indx smallint
declare @table table
(COL1 varchar(5) ,COL2 varchar(5))
set @string = '1^2~3^4~5^6~7^8'
Set @found=1
while @found=1
begin
Set @indx = charindex('~',@string)
if @indx > 0
begin
Set @found = 1
set @tmpstr=substring(@string,1,@indx-1)
insert into @table(COL1,COL2)
select substring(@tmpstr,1,1),substring(@tmpstr,3,3)
Set @string = substring(@string,@indx+1,50000)
end
else
Set @found=0
end
insert into @table(COL1,COL2)
select substring(@string,1,1),substring(@string,3,3)
select * from @table
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 6, 2012 at 12:06 am
Using Jeff Moden's string splitter: http://www.sqlservercentral.com/articles/Tally+Table/72993/, you can do it like this:
DECLARE @STR VARCHAR(20) = '1^2~3^4~5^6'
SELECT Col1=MAX(CASE WHEN t.ItemNumber = 1 THEN t.Item END)
,Col2=MAX(CASE WHEN t.ItemNumber = 2 THEN t.Item END)
FROM dbo.DelimitedSplit8K(@str, '~') s
CROSS APPLY dbo.DelimitedSplit8K(item, '^') t
GROUP BY s.ItemNumber
However if you have a varying number of elements in your string that will make it significantly more challenging as you'll need to create some kind of dynamic cross tab.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply