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