October 21, 2010 at 2:06 am
Hi Friends,
Declare @t Table
(
id int,
EmpName nvarchar(500),
status char(5),
Repair int
)
Insert Into @t
Select 1,'Arun,Balu','Yes',25 Union All
Select 2,'Nagu','No',20 Union All
Select 3,'Mani','No',45 Union All
Select 4,'XXX,YYY','No',5
select * from @t
My Required Output is Like This
id EmpName status repair
1ArunYes 25
1BaluYes 25
2NaguNo 20
3ManiNo 45
4XXX No 5
4YYYNo 5
October 21, 2010 at 4:17 am
SELECT s.*, x.*
FROM @t s
CROSS APPLY (
SELECT EmpNamePart = SUBSTRING(s.EmpName, b, e), b, e
FROM (
SELECT
n.n AS b, -- delimiter at beginning of string
ISNULL(NULLIF(CHARINDEX(',', s.EmpName, n.n+1), 0), LEN(s.EmpName)+1)-1 AS e -- delimiter at end of string
FROM (SELECT TOP 200 [n] = ROW_NUMBER() OVER(ORDER BY [name]) FROM master.dbo.syscolumns) n -- generates 200 rows where n = 1 to 200
WHERE SUBSTRING(',' + s.EmpName + ',', n.n, 1) = ','
AND n < LEN(s.EmpName)
) d
) x
ORDER BY s.ID
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
October 21, 2010 at 4:32 am
There are several ways to split strings in addition to the method Chris mentioned. Google "SQL Server Split Strings," choose one that seems to work, and apply that methodology to your code.
Just an FYI: if you don't understand what Chris did, research it and ask questions. Don't just use the code without understanding it. It'll get you into trouble when something breaks and you can't tell your boss why you did what you did.
October 21, 2010 at 5:08 am
Really i didn't understand the solution that is posted here.Please can some one explain me what exactly the Query is doing.
October 21, 2010 at 5:22 am
sharath.chalamgari (10/21/2010)
Really i didn't understand the solution that is posted here.Please can some one explain me what exactly the Query is doing.
Sure. Here's a start:
-- create a sequence table (this would ideally be properly formed with a clustered index)
DROP TABLE #Numbers
CREATE TABLE #Numbers (n INT)
INSERT INTO #Numbers (n)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
SELECT 16
-- what does it look like?
SELECT *
FROM #Numbers
-- Create and populate a string variable.
-- Commas are in positions 2 and 8 within the string
DECLARE @MyString VARCHAR(16)
SET @MyString = 'a,short,string'
-- Query the sequence (numbers) table, restricting the output
-- to numbers which are the positions of commas in the string
SELECT *
FROM #Numbers Nos
WHERE SUBSTRING(@MyString, Nos.n, 1) = ','
-- retrieve everything before each comma:
SELECT LEFT(@MyString, Nos.n - 1)
FROM #Numbers Nos
WHERE SUBSTRING(@MyString, Nos.n, 1) = ','
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
October 21, 2010 at 6:04 am
Thanks Chris now i got the idea of how you achived this.
October 21, 2010 at 6:37 am
Chris Morris-439714 (10/21/2010)
SELECT s.*, x.*FROM @t s
CROSS APPLY (
SELECT EmpNamePart = SUBSTRING(s.EmpName, b, e), b, e
FROM (
SELECT
n.n AS b, -- delimiter at beginning of string
ISNULL(NULLIF(CHARINDEX(',', s.EmpName, n.n+1), 0), LEN(s.EmpName)+1)-1 AS e -- delimiter at end of string
FROM (SELECT TOP 200 [n] = ROW_NUMBER() OVER(ORDER BY [name]) FROM master.dbo.syscolumns) n -- generates 200 rows where n = 1 to 200
WHERE SUBSTRING(',' + s.EmpName + ',', n.n, 1) = ','
AND n < LEN(s.EmpName)
) d
) x
ORDER BY s.ID
Hi Chirs,
Really super your coding and techinque. But i have small issue in this code
For Eg
Declare @t Table
(
id int,
EmpName nvarchar(500),
status char(5),
Repair int
)
Insert Into @t
Select 2,'Nagu','No',20 Union All
Select 4,'XXX,YYY','No',5 Union all
Select 5,'Anil, Rakesh, Deep','No',50
SELECT s.*, x.*
FROM @t s
CROSS APPLY (
SELECT EmpNamePart = SUBSTRING(s.EmpName, b, e), b, e
FROM (
SELECT
n.n AS b, -- delimiter at beginning of string
ISNULL(NULLIF(CHARINDEX(',', s.EmpName, n.n+1), 0), LEN(s.EmpName)+1)-1 AS e -- delimiter at end of string
FROM
(SELECT TOP 200 [n] = ROW_NUMBER() OVER(ORDER BY [name]) FROM master.dbo.syscolumns) n -- generates 200 rows where n = 1 to 200
WHERE SUBSTRING(',' + s.EmpName + ',', n.n, 1) = ','
AND n < LEN(s.EmpName)
) d
) x
ORDER BY s.ID
Am Getting Ouput Like This
2Nagu No 20Nagu 14
4XXX,YYY No 5XXX 13
4XXX,YYY No 5YYY 57
5Anil, Rakesh, DeepNo 50 Rakesh, Dee612
5Anil, Rakesh, DeepNo 50 Deep 1418
5Anil, Rakesh, DeepNo 50Anil 14
From There I need to Bring result Rakesh Alone Like this
Anil, Rakesh, DeepNo 50 Rakesh612
Even though i have made changes in above code
SELECT EmpNamePart = SUBSTRING(s.EmpName, b, e-b+1), b, e
From this situation When send value like 's,Rajesh,Mani'
Here S -Value get ignored.
Can you please guide, how can i do this
October 21, 2010 at 6:56 am
Humble apologies. Revised code attached.
Declare @t Table
(
id int,
EmpName nvarchar(500),
status char(5),
Repair int
)
Insert Into @t
Select 2,'Nagu','No',20 Union All
Select 4,'XXX,YYY','No',5 Union all
Select 5,'Anil, Rakesh, Deep','No',50
SELECT s.*, x.*
FROM @t s
CROSS APPLY (
SELECT EmpNamePart = SUBSTRING(s.EmpName, b, e-b+1),
SectionBeginning = b,
SectionEnd = e,
SectionLength = e-b+1
FROM (
SELECT
n.n AS b, -- delimiter at beginning of string
ISNULL(NULLIF(CHARINDEX(',', s.EmpName, n.n+1), 0), LEN(s.EmpName)+1)-1 AS e -- delimiter at end of string
FROM
(SELECT TOP 200 [n] = ROW_NUMBER() OVER(ORDER BY [name]) FROM master.dbo.syscolumns) n -- generates 200 rows where n = 1 to 200
WHERE SUBSTRING(',' + s.EmpName + ',', n.n, 1) = ','
AND n < LEN(s.EmpName)
) d
) x
ORDER BY s.ID
May I suggest that when you are familiar with how the code works, the "teaching aid" stuff is removed? It would look something like this:
Declare @t Table
(
id int,
EmpName nvarchar(500),
status char(5),
Repair int
)
Insert Into @t
Select 2,'Nagu','No',20 Union All
Select 4,'XXX,YYY','No',5 Union all
Select 5,'Anil, Rakesh, Deep','No',50
SELECT s.id, EmpName = LTRIM(x.EmpNamePart), s.Status, s.Repair
FROM @t s
CROSS APPLY (
SELECT EmpNamePart = SUBSTRING(s.EmpName, n.n, ISNULL(NULLIF(CHARINDEX(',', s.EmpName, n.n+1), 0), LEN(s.EmpName)+1)-1-n.n+1)
FROM (SELECT TOP 200 [n] = ROW_NUMBER() OVER(ORDER BY [name]) FROM master.dbo.syscolumns) n -- create and use a proper table
WHERE SUBSTRING(',' + s.EmpName + ',', n.n, 1) = ',' AND n < LEN(s.EmpName)
) x (EmpNamePart)
ORDER BY s.ID
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
October 21, 2010 at 10:04 am
Here is my version, which is 80% based on Chris' logic of using a tally table 🙂
;WITH Tens(N) AS
(
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1
),
HUNDREDS AS
(
SELECT T1.N FROM TENS T1 CROSS JOIN TENS T2
),
THOUSANDS AS
(
SELECT T1.N FROM HUNDREDS T1 CROSS JOIN HUNDREDS T2
),
Numbers AS
(
SELECT N = (ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ) FROM THOUSANDS
)
SELECT T.*, LTRIM ( SUBSTRING(EmpName, N, (CHARINDEX(',', EmpName + ',', N) - N)) ) AS Item
FROM Numbers , @t T
WHERE N < LEN(EmpName) + 2
AND SUBSTRING(',' + EmpName , N, 1) = ','
ORDER BY id
~Edit : Fixed leading spaces from the split string.
October 21, 2010 at 10:06 am
And i presume your EmpName does not have trailin/leading commas.. Else we will have to use another method.
October 21, 2010 at 10:09 am
ColdCoffee (10/21/2010)
And i presume your EmpName does not have trailin/leading commas.. Else we will have to use another method.
There's a trick for that...put them in then
REPLACE(',' + EmpName + ',',',,',',')
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
October 21, 2010 at 10:11 am
And i must admit that my code is purely what the DelimitedSplit8K (function from Jeff Moden on splitting delimited strings) does!! So toss ur credits to Jeff Moden and the people behind that awesome splitter..
October 21, 2010 at 10:26 am
Chris Morris-439714 (10/21/2010)
ColdCoffee (10/21/2010)
And i presume your EmpName does not have trailin/leading commas.. Else we will have to use another method.There's a trick for that...put them in then
REPLACE(',' + EmpName + ',',',,',',')
fantastic thought CM, but there is a catch here...
Assume the string is like this
',an,empty,space,comes,between,this,and, ,this,'
Then the REPLACE will collapse the string... correct me if am wrong..
October 21, 2010 at 10:30 am
What about this?
select reverse(stuff( reverse(stuff(@string,1,1,'')) ,1,1,''))
Ugly, but still 😛
October 21, 2010 at 11:26 pm
Wiht the Chris idea on the cross apply and my previous split function that used for other perpose
i have written like this :
Declare @t Table
(
id int,
EmpName nvarchar(500),
status char(5),
Repair int
)
Insert Into @t
Select 1,'Arun,Balu','Yes',25 Union All
Select 2,'Nagu','No',20 Union All
Select 3,'Mani','No',45 Union All
Select 4,'XXX,YYY','No',5 union all
Select 5,'Anil, Rakesh, Deep','No',50
select t1.id,t1.Empname,t2.id,t2.Param from @t as t1
Cross apply dbo.Fn_Split(t1.id,t1.Empname,',') as t2
and the Sql Function is like
-- Split Function
-- i am passing id which can be your any column values
If Exists (Select Name from Sysobjects where name='Fn_Split' And xtype='TF')
Begin
Drop function dbo.Fn_Split
End
Go
CREATE FUNCTION dbo.Fn_Split(@id int,@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (id int,Param varchar(100))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(id,Param) VALUES(@id,@Piece)
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
GO
1Arun,Balu1Arun
1Arun,Balu1Balu
2Nagu2Nagu
3Mani3Mani
4XXX,YYY4XXX
4XXX,YYY4YYY
5Anil, Rakesh, Deep5Anil
5Anil, Rakesh, Deep5 Rakesh
5Anil, Rakesh, Deep5 Deep
Please let me know if i am wrong in any way
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply