August 5, 2015 at 3:09 am
Dear All ,
declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
---------------------
Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)
select * from #tmp
I want to convert to @var to same like #tmp table ..
"@" - delimiter goes to rows
"~" - delimiter goes to columns
Kindly advise , Thanks in advance
August 5, 2015 at 3:29 am
Not very pretty, but it will work as long as you don't have any apostrophes in your string. There is a good chance that other inputs should also be handled, but I think that this can show you the direction of how to do it without looping through the string.
declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
select @var = 'INSERT INTO #tmp (Name, Value1, Value2, Value3) Values (''' +
replace(replace(@var,'~',''','''),'@', '''); INSERT INTO #tmp (Name, Value1, Value2, Value3) Values (''')
+ ''')'
select @var
---------------------
Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
--Insert into #tmp (Name,Value1,Value2,Value3)
--Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)
exec (@var)
select * from #tmp
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 5, 2015 at 4:22 am
Hi , Thanks for you update.
I am looking for like a function to insert data like below instead of creating insert statement in your query.
select Name,Value1,Value2,Value3 from functionSplitter(@var, '@','~')
Thanks,
August 5, 2015 at 6:09 am
s.chandrahasan (8/5/2015)
Hi , Thanks for you update.I am looking for like a function to insert data like below instead of creating insert statement in your query.
select Name,Value1,Value2,Value3 from functionSplitter(@var, '@','~')
Thanks,
You could easily turn this into a splitter function:
DECLARE @var VARCHAR(8000)
SET @var = 'Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
;WITH
n1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n = 0 FROM n1, n1 x),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM n2, n2 x)
,
MyRowChopper AS ( -- find the position of each '@'
SELECT
Start = ISNULL(LAG(n,1) OVER(ORDER BY n),0),
[End] = n,
MyString
FROM (SELECT MyString = @var) d
INNER JOIN iTally t ON SUBSTRING(MyString,n,1) = '@'
AND t.n <= LEN(@var)
)
SELECT
--Start,
--[End],
MyString,
r.MyRow,
Col1 = SUBSTRING(r.MyRow,1,p1.n-1),
Col2 = SUBSTRING(r.MyRow,p1.n+1,p2.n-p1.n-1),
Col3 = SUBSTRING(r.MyRow,p2.n+1,p3.n-p2.n-1),
Col4 = SUBSTRING(r.MyRow,p3.n+1,8000)
FROM MyRowChopper
CROSS APPLY (SELECT MyRow = SUBSTRING(MyString, start+1, [end]-start-1)) r
CROSS APPLY (SELECT n = CHARINDEX('~',MyRow,1)) p1
CROSS APPLY (SELECT n = CHARINDEX('~',MyRow,p1.n+1)) p2
CROSS APPLY (SELECT n = CHARINDEX('~',MyRow,p2.n+1)) p3
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2015 at 7:26 am
ChrisM@Work (8/5/2015)
s.chandrahasan (8/5/2015)
Hi , Thanks for you update.I am looking for like a function to insert data like below instead of creating insert statement in your query.
select Name,Value1,Value2,Value3 from functionSplitter(@var, '@','~')
Thanks,
You could easily turn this into a splitter function:
DECLARE @var VARCHAR(8000)SET @var = 'Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
;WITH
n1 AS (SELECT * FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
n2 AS (SELECT n = 0 FROM n1, n1 x),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM n2, n2 x)
,
MyRowChopper AS ( -- find the position of each '@'
SELECT
Start = ISNULL(LAG(n,1) OVER(ORDER BY n),0),
[End] = n,
MyString
FROM (SELECT MyString = @var) d
INNER JOIN iTally t ON SUBSTRING(MyString,n,1) = '@'
AND t.n <= LEN(@var)
)
SELECT
--Start,
--[End],
MyString,
r.MyRow,
Col1 = SUBSTRING(r.MyRow,1,p1.n-1),
Col2 = SUBSTRING(r.MyRow,p1.n+1,p2.n-p1.n-1),
Col3 = SUBSTRING(r.MyRow,p2.n+1,p3.n-p2.n-1),
Col4 = SUBSTRING(r.MyRow,p3.n+1,8000)
FROM MyRowChopper
CROSS APPLY (SELECT MyRow = SUBSTRING(MyString, start+1, [end]-start-1)) r
CROSS APPLY (SELECT n = CHARINDEX('~',MyRow,1)) p1
CROSS APPLY (SELECT n = CHARINDEX('~',MyRow,p1.n+1)) p2
CROSS APPLY (SELECT n = CHARINDEX('~',MyRow,p2.n+1)) p3
Very impressive
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 5, 2015 at 8:20 am
s.chandrahasan (8/5/2015)
Dear All ,
declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
---------------------
Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)
select * from #tmp
I want to convert to @var to same like #tmp table ..
"@" - delimiter goes to rows
"~" - delimiter goes to columns
Kindly advise , Thanks in advance
Jeff Moden has written the definitive string splitter function, called DelimitedSplit8K, and it has it's own article on this site, where the words "Tally Oh" are in the title. Look up that article and read it, here[/url]:
It can easily be used on your example data, and if you know the number of columns in advance, and generic column names are ok, you can use the following approach that makes use of that function:
DECLARE @var varchar(8000) = 'Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23';
WITH ALL_DATA AS (
SELECT R.ItemNumber AS ROW_NUM, 'COL' + CAST(C.ItemNumber AS varchar(2)) AS COLUMN_NAME, C.Item AS Value
FROM dbo.fnDelimitedSplit8K(@var, '@') AS R
CROSS APPLY dbo.fnDelimitedSplit8K(R.Item, '~') AS C
)
SELECT ROW_NUM, [COL1], [COL2], [COL3], [COL4]
FROM ALL_DATA
PIVOT (MAX(Value) FOR COLUMN_NAME IN ([COL1], [COL2], [COL3], [COL4])) AS PVT
Here's the results:
ROW_NUMCOL1COL2COL3COL4
------- ------- ------- ------- -------
1Name1502050
2Name225.55063
3Name3308043
4Name4608023
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2015 at 8:46 am
A slight twist on Steve's:
declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
SELECT ds1.ItemNumber AS RowNumber,
MAX(CASE WHEN ds2.ItemNumber = 1 THEN ds2.Item ELSE NULL END) AS Col1,
MAX(CASE WHEN ds2.ItemNumber = 2 THEN ds2.Item ELSE NULL END) AS Col2,
MAX(CASE WHEN ds2.ItemNumber = 3 THEN ds2.Item ELSE NULL END) AS Col3,
MAX(CASE WHEN ds2.ItemNumber = 4 THEN ds2.Item ELSE NULL END) AS Col4
FROM dbo.DelimitedSplit8K(@var, '@') ds1
CROSS APPLY dbo.DelimitedSplit8K(ds1.Item, '~') ds2
GROUP BY ds1.ItemNumber;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply