· 8 years ago · Aug 18, 2018, 09:22 AM
1split comma separated values into distinct rows
2id fk_det userid
33 9 name1,name2
46 1 name3
59 2 name4,name5
612 3 name6,name7
7
8id fk_det userid
93 9 name1
10x 9 name2
116 1 name3
129 2 name4
13x 2 name5
1412 3 name6
15x 3 name7
16
17select fk_det, det, LEFT(userid, CHARINDEX(',',userid+',')-1),
18 STUFF(userid, 1, CHARINDEX(',',userid+','), '')
19from global_permissions
20
21IF EXISTS (
22 SELECT 1
23 FROM dbo.sysobjects
24 WHERE id = object_id(N'[dbo].[ParseString]')
25 AND xtype in (N'FN', N'IF', N'TF'))
26BEGIN
27 DROP FUNCTION [dbo].[ParseString]
28END
29GO
30
31CREATE FUNCTION dbo.ParseString (@String VARCHAR(8000), @Delimiter VARCHAR(10))
32RETURNS TABLE
33AS
34/*******************************************************************************************************
35* dbo.ParseString
36*
37* Creator: magicmike
38* Date: 9/12/2006
39*
40*
41* Outline: A set-based string tokenizer
42* Takes a string that is delimited by another string (of one or more characters),
43* parses it out into tokens and returns the tokens in table format. Leading
44* and trailing spaces in each token are removed, and empty tokens are thrown
45* away.
46*
47*
48* Usage examples/test cases:
49 Single-byte delimiter:
50 select * from dbo.ParseString2('|HDI|TR|YUM|||', '|')
51 select * from dbo.ParseString2('HDI| || TR |YUM', '|')
52 select * from dbo.ParseString2(' HDI| || S P A C E S |YUM | ', '|')
53 select * from dbo.ParseString2('HDI|||TR|YUM', '|')
54 select * from dbo.ParseString2('', '|')
55 select * from dbo.ParseString2('YUM', '|')
56 select * from dbo.ParseString2('||||', '|')
57 select * from dbo.ParseString2('HDI TR YUM', ' ')
58 select * from dbo.ParseString2(' HDI| || S P A C E S |YUM | ', ' ') order by Ident
59 select * from dbo.ParseString2(' HDI| || S P A C E S |YUM | ', ' ') order by StringValue
60
61 Multi-byte delimiter:
62 select * from dbo.ParseString2('HDI and TR', 'and')
63 select * from dbo.ParseString2('Pebbles and Bamm Bamm', 'and')
64 select * from dbo.ParseString2('Pebbles and sandbars', 'and')
65 select * from dbo.ParseString2('Pebbles and sandbars', ' and ')
66 select * from dbo.ParseString2('Pebbles and sand', 'and')
67 select * from dbo.ParseString2('Pebbles and sand', ' and ')
68*
69*
70* Notes:
71 1. A delimiter is optional. If a blank delimiter is given, each byte is returned in it's own row (including spaces).
72 select * from dbo.ParseString3('|HDI|TR|YUM|||', '')
73 2. In order to maintain compatibility with SQL 2000, ident is not sequential but can still be used in an order clause
74 If you are running on SQL2005 or later
75 SELECT Ident, StringValue FROM
76 with
77 SELECT Ident = ROW_NUMBER() OVER (ORDER BY ident), StringValue FROM
78*
79*
80* Modifications
81*
82*
83********************************************************************************************************/
84RETURN (
85SELECT Ident, StringValue FROM
86 (
87 SELECT Num as Ident,
88 CASE
89 WHEN DATALENGTH(@delimiter) = 0 or @delimiter IS NULL
90 THEN LTRIM(SUBSTRING(@string, num, 1)) --replace this line with '' if you prefer it to return nothing when no delimiter is supplied. Remove LTRIM if you want to return spaces when no delimiter is supplied
91 ELSE
92 LTRIM(RTRIM(SUBSTRING(@String,
93 CASE
94 WHEN (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) <> @delimiter) THEN 1
95 ELSE Num + DATALENGTH(@delimiter)
96 END,
97 CASE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter))
98 WHEN 0 THEN LEN(@String) - Num + DATALENGTH(@delimiter)
99 ELSE CHARINDEX(@Delimiter, @String, Num + DATALENGTH(@delimiter)) - Num -
100 CASE
101 WHEN Num > 1 OR (Num = 1 AND SUBSTRING(@String,num ,DATALENGTH(@delimiter)) = @delimiter)
102 THEN DATALENGTH(@delimiter)
103 ELSE 0
104 END
105 END
106 )))
107 End AS StringValue
108 FROM dbo.Numbers
109 WHERE Num <= LEN(@String)
110 AND (
111 SUBSTRING(@String, Num, DATALENGTH(ISNULL(@delimiter,''))) = @Delimiter
112 OR Num = 1
113 OR DATALENGTH(ISNULL(@delimiter,'')) = 0
114 )
115 ) R WHERE StringValue <> ''
116)
117
118SELECT id, pk_det, V.StringValue as userid
119FROM myTable T
120OUTER APPLY dbo.ParseString(T.userId) V
121
122IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Numbers')
123BEGIN
124
125 CREATE TABLE dbo.Numbers
126 (
127 Num INT NOT NULL
128 CONSTRAINT [PKC__Numbers__Num] PRIMARY KEY CLUSTERED (Num) on [PRIMARY]
129 )
130 ;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
131 Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
132 Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
133 Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
134 Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
135
136 INSERT INTO dbo.Numbers(Num)
137 SELECT n
138 FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
139 FROM Nbrs ) D ( n )
140 WHERE n <= 50000 ;
141END
142
143with temp as(
144select id,fk_det,cast('<comma>'+replace(userid,',','</comma><comma>')+'</comma>' as XMLcomma
145from global_permissions
146)
147
148select id,fk_det,a.value('comma[1]','varchar(512)')
149cross apply temp.XMLcomma.nodes('/comma') t(a)
150
151DECLARE @Name TABLE
152 (
153 id INT NULL ,
154 fk_det INT NULL ,
155 userid NVARCHAR(100) NULL
156 )
157
158INSERT INTO @Name
159 ( id, fk_det, userid)
160VALUES (3,9,'name1,name2' )
161
162INSERT INTO @Name
163 ( id, fk_det, userid)
164VALUES (6,1,'name3' )
165
166INSERT INTO @Name
167 ( id, fk_det, userid)
168VALUES (9,2,'name4,name5' )
169
170INSERT INTO @Name
171 ( id, fk_det, userid)
172VALUES (12,3,'name6,name7' )
173
174SELECT *
175FROM @Name
176
177 SELECT id,A.fk_det,
178 Split.a.value('.', 'VARCHAR(100)') AS String
179 FROM (SELECT id,fk_det,
180 CAST ('<M>' + REPLACE(userid, ',', '</M><M>') + '</M>' AS XML) AS String
181 FROM @Name) AS A CROSS APPLY String.nodes ('/M') AS Split(a);