· 8 years ago · May 20, 2018, 05:34 PM
1110,200,310,130,null
2
3create FUNCTION [dbo].[fn_splitstring]
4(
5 @List nvarchar(2000),
6 @SplitOn nvarchar(5)
7)
8RETURNS @RtnValue table
9(
10 Id int identity(1,1),
11 Value nvarchar(100)
12)
13AS
14BEGIN
15 while (Charindex(@SplitOn,@List)>0)
16 begin
17 insert into @RtnValue (value)
18 select
19 Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
20
21 set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
22 end
23 insert Into @RtnValue (Value)
24 select Value = ltrim(rtrim(@List))
25
26 return
27END
28
29select Val , (select value from tvf_split_string(cchar1,',')) from table1
30
31select Val ,
32(select value from tvf_split_string(cchar1,',') order by id offset 0 rows fetch next 1 rows only ) as col1,
33(select value from tvf_split_string(cchar1,',') order by id offset 1 rows fetch next 1 rows only ) as col2,
34................
35 from table1
36
37aaa,111,zzz
38
39select split(csv, 1, 2) ... ;
40 -- 1: start at the first comma
41 -- 2: end at the second comma
42
43select split(csv, 0, 1) ... ; -- from the start of the string (no comma) up to the first comma
44 select split(csv, 2, 0) ... ; -- from the second comma right up to the end of the string
45
46create or replace function split(
47 csvstring varchar2
48, lcpos number
49, rcpos number )
50return varchar2
51is
52 slen pls_integer := 0 ; -- string length
53 comma constant varchar2(1) := ',' ;
54 currentchar varchar2(1) := '' ;
55 commacount pls_integer := 0 ;
56 firstcommapos pls_integer := 0 ;
57 secondcommapos pls_integer := 0 ;
58begin
59
60 slen := length(csvstring);
61
62 -- special case: leftmost value
63 if lcpos = 0 then
64 firstcommapos := 0 ;
65 for i in 1 .. slen
66 loop
67 currentchar := substr(csvstring, i, 1) ;
68 if currentchar = comma then
69 secondcommapos := i - 1 ;
70 exit ;
71 end if ;
72 end loop ;
73 return substr(csvstring, 1, secondcommapos) ;
74 end if ;
75
76 -- 2 commas somewhere in the middle of the string
77 if lcpos > 0 and rcpos > 0 then
78 for i in 1 .. slen
79 loop
80 currentchar := substr(csvstring, i, 1) ;
81 if currentchar = comma then
82 commacount := commacount + 1;
83 if commacount = lcpos then
84 firstcommapos := i ;
85 end if ;
86 if commacount = rcpos then
87 secondcommapos := i ;
88 end if ;
89 end if ;
90 end loop ;
91 return substr(csvstring, firstcommapos + 1, (secondcommapos-1) - firstcommapos ) ;
92 end if ;
93
94 -- special case: rightmost value
95 if rcpos = 0 then
96 secondcommapos := slen ;
97 for i in reverse 1 .. slen -- caution: count DOWN!
98 loop
99 currentchar := substr(csvstring, i, 1) ;
100 if currentchar = comma then
101 firstcommapos := i + 1 ;
102 exit ;
103 end if ;
104 end loop ;
105 return substr(csvstring, firstcommapos, secondcommapos-(firstcommapos-1)) ;
106 end if ;
107
108end split;
109
110-- test table, test data
111create table csv (
112 id number generated always as identity primary key
113, astring varchar2(256)
114);
115
116-- insert some test data
117begin
118 insert into csv (astring) values ('123,456,88789,null,null');
119 insert into csv (astring) values ('123,456,99789,1234,null');
120 insert into csv (astring) values ('123,456,00789,1234,null');
121 insert into csv (astring) values ('1,2222,77789,null,null');
122 insert into csv (astring) values ('11,222,88789,null,');
123 insert into csv (astring) values ('111,22,99789,,');
124 insert into csv (astring) values ('1111,2,00789,oooo,null');
125end;
126
127-- testing:
128select
129 split(astring,0,1) col1
130, split(astring,1,2) col2
131, split(astring,2,3) col3
132, split(astring,3,4) col4
133, split(astring,4,0) col5
134from csv
135
136-- output
137COL1 COL2 COL3 COL4 COL5
138123 456 88789 null null
139123 456 99789 1234 null
140123 456 00789 1234 null
1411 2222 77789 null null
14211 222 88789 null -
143111 22 99789 - -
1441111 2 00789 oooo null
145
146--===== If the test table exists, drop it to make reruns in SSMS eaiser
147 IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
148 DROP TABLE #CSV
149;
150--===== Create the test table.
151 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
152 (
153 ID INT IDENTITY(1,1) --or whatever your PK is
154 ,AString VARCHAR(8000)
155 )
156 ;
157--===== Insert some test data
158 INSERT INTO #CSV
159 (AString)
160 SELECT AString
161 FROM (
162 VALUES ('123,456,88789,null,null')
163 ,('123,456,99789,1234,null')
164 ,('123,456,00789,1234,null')
165 ,('1,2222,77789,null,null')
166 ,('11,222,88789,null,')
167 ,('111,22,99789,,')
168 ,('1111,2,00789,oooo,null')
169 ) v (AString)
170;
171
172--===== If the test table exists, drop it to make reruns in SSMS eaiser
173 IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
174 DROP TABLE #CSV
175;
176--===== Create the test table.
177 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
178 (
179 ID INT IDENTITY(1,1) --or whatever your PK is
180 ,AString VARCHAR(8000)
181 )
182 ;
183--===== Insert some test data
184 INSERT INTO #CSV
185 (AString)
186 SELECT AString
187 FROM (
188 SELECT '123,456,88789,null,null' UNION ALL
189 SELECT '123,456,99789,1234,null' UNION ALL
190 SELECT '123,456,00789,1234,null' UNION ALL
191 SELECT '1,2222,77789,null,null' UNION ALL
192 SELECT '11,222,88789,null,' UNION ALL
193 SELECT '111,22,99789,,' UNION ALL
194 SELECT '1111,2,00789,oooo,null'
195 ) v (AString)
196;
197
198CREATE FUNCTION [dbo].[DelimitedSplit8K]
199/**********************************************************************************************************************
200 Purpose:
201 Given a string containing multiple elements separated by a single character delimiter and that single character
202 delimiter, this function will split the string and return a table of the single elements (Item) and the element
203 position within the string (ItemNumber).
204
205 Notes:
206 1. Performance of this function approaches that of a CLR.
207 2. Note that this code implicitly converts NVARCHAR to VARCHAR and that conversion may NOT be faithful.
208
209 Revision History:
210 Note that this code is a modification of a well proven function created as a community effort and initially documented
211 at the following URL (http://www.sqlservercentral.com/articles/Tally+Table/72993/). This code is still undergoing
212 tests. Although every care has certainly been taken to ensure its accuracy, you are reminded to do your own tests to
213 ensure that this function is suitable for whatever application you might use it for.
214 --Jeff Moden, 01 Sep 2013
215**********************************************************************************************************************/
216--===== Define I/O parameters
217 (@pString VARCHAR(8000) , @pDelimiter CHAR(1)) --DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
218RETURNS TABLE WITH SCHEMABINDING AS
219 RETURN
220--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
221 -- enough to cover VARCHAR(8000).
222 WITH E1(N) AS (--==== Itzik Ben-Gan style of a cCTE (Cascading CTE) and
223 -- should not be confused with a much slower rCTE (Recursive CTE).
224 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
225 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
226 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
227 ), --10E+1 or 10 rows
228 E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), --10E+4 or 10,000 rows max
229 cteTally(N) AS ( --=== This provides the "base" CTE and limits the number of rows right up front
230 -- for both a performance gain and prevention of accidental "overruns"
231 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
232 ),
233cteStart(N1) AS ( --=== This returns N+1 (starting position of each "element" just once for each delimiter)
234 SELECT 1 UNION ALL
235 SELECT CASE WHEN SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN THEN t.N+1 END --added short circuit for casting
236 FROM cteTally t
237 WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN
238 ),
239cteLen(N1,L1)AS ( --=== Return start position and length (for use in substring).
240 -- The ISNULL/NULLIF combo handles the length for the final of only element.
241 SELECT s.N1,
242 ISNULL(NULLIF(CHARINDEX(@pDelimiter ,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)
243 FROM cteStart s
244 )
245--===== Do the actual split.
246 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
247 Item = SUBSTRING(@pString, l.N1, l.L1)
248 FROM cteLen l
249;
250GO
251
252--===== Do the split for each row and repivot to columns using a
253 -- high performance CROSS TAB. It uses the function only once
254 -- for each row, which is another advantage iTVFs have over
255 -- Scalare Functions.
256 SELECT csv.ID
257 ,Col1 = MAX(CASE WHEN ca.ItemNumber = 1 THEN Item ELSE '' END)
258 ,Col2 = MAX(CASE WHEN ca.ItemNumber = 2 THEN Item ELSE '' END)
259 ,Col3 = MAX(CASE WHEN ca.ItemNumber = 3 THEN Item ELSE '' END)
260 ,Col4 = MAX(CASE WHEN ca.ItemNumber = 4 THEN Item ELSE '' END)
261 ,Col5 = MAX(CASE WHEN ca.ItemNumber = 5 THEN Item ELSE '' END)
262 FROM #CSV csv
263 CROSS APPLY dbo.DelimitedSplit8K(csv.AString,',') ca
264 GROUP BY csv.ID
265;
266GO