1
--
-去掉字符串分隔符
2
3 CREATE TABLE inventory (fullname varchar ( 60 ) NOT NULL )
4 go
5 INSERT inventory(fullname)
6 VALUES ( ' 2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094 ' )
7 INSERT inventory(fullname)
8 VALUES ( ' 2004 Inventory ' )
9 INSERT inventory(fullname)
10 VALUES ( ' 2004 Inventory:Ex.Plant Farm1:1st Cut ' )
11 go
12
13 SELECT inventory, plant = nullif (plant, '' ), cut = nullif (cut, '' ),
14 grade = nullif (grade, '' ), lot# = nullif (lot#, '' )
15 FROM
16 ( SELECT inventory, plant, cut,
17 left (rest, charindex ( ' : ' , rest + ' : ' ) - 1 ) AS grade,
18 substring (rest, charindex ( ' : ' , rest + ' : ' ) + 1 ,
19 len (rest)) AS lot#
20 FROM
21 ( SELECT inventory, plant,
22 left (rest, charindex ( ' : ' , rest + ' : ' ) - 1 ) AS cut,
23 substring (rest, charindex ( ' : ' , rest + ' : ' ) + 1 ,
24 len (rest)) AS rest
25 FROM
26 ( SELECT inventory,
27 left (rest, charindex ( ' : ' , rest + ' : ' ) - 1 ) AS plant,
28 substring (rest, charindex ( ' : ' , rest + ' : ' ) + 1 ,
29 len (rest)) AS rest
30 FROM
31 ( SELECT left (fullname, charindex ( ' : ' , fullname + ' : ' ) - 1 ) AS inventory,
32 substring (fullname, charindex ( ' : ' , fullname + ' : ' ) + 1 ,
33 len (fullname)) AS rest
34 FROM inventory) AS a) AS b) AS c) AS d
35 go
36
37 DROP TABLE inventory
38
39 -- Below is a UDF to take a comma delim value and return in table format,
40 -- the split results.. Use like select * from dbo.Split('1,2,3,3',',')
41 CREATE FUNCTION dbo.Split( @sText varchar ( 8000 ), @sDelim varchar ( 20 ) = '' )
42 RETURNS @retArray TABLE (idx smallint Primary Key , value varchar ( 8000 ))
43 AS
44 BEGIN
45 DECLARE @idx int ,
46 @value varchar ( 8000 ),
47 @bcontinue bit ,
48 @iStrike int ,
49 @iDelimlength int
50
51 IF @sDelim = ' Space '
52 BEGIN
53 SET @sDelim = ' '
54 END
55
56 SET @idx = 0
57 SET @sText = LTrim ( RTrim ( @sText ))
58 SET @iDelimlength = DATALENGTH ( @sDelim )
59 SET @bcontinue = 1
60
61 if ( Len ( @sText ) = 0 )
62 return
63
64 IF NOT (( @iDelimlength = 0 ) or ( @sDelim = ' Empty ' ))
65 BEGIN
66 WHILE @bcontinue = 1
67 BEGIN
68
69 -- If you can find the delimiter in the text, retrieve the first element
70 and
71 -- insert it with its index into the return table.
72
73 IF CHARINDEX ( @sDelim , @sText ) > 0
74 BEGIN
75 SET @value = SUBSTRING ( @sText , 1 , CHARINDEX ( @sDelim , @sText ) - 1 )
76 BEGIN
77 INSERT @retArray (idx, value)
78 VALUES ( @idx , @value )
79 END
80
81 -- Trim the element and its delimiter from the front of the string.
82 -- Increment the index and loop.
83 SET @iStrike = DATALENGTH ( @value ) + @iDelimlength
84 SET @idx = @idx + 1
85 SET @sText = LTrim ( Right ( @sText , DATALENGTH ( @sText ) - @iStrike ))
86
87 END
88 ELSE
89 BEGIN
90 -- If you canÆt find the delimiter in the text, @sText is the last
91 value in
92 -- @retArray.
93 SET @value = @sText
94 BEGIN
95 INSERT @retArray (idx, value)
96 VALUES ( @idx , @value )
97 END
98 -- Exit the WHILE loop.
99 SET @bcontinue = 0
100 END
101 END
102 END
103 ELSE
104 BEGIN
105 WHILE @bcontinue = 1
106 BEGIN
107 -- If the delimiter is an empty string, check for remaining text
108 -- instead of a delimiter. Insert the first character into the
109 -- retArray table. Trim the character from the front of the string.
110 -- Increment the index and loop.
111 IF DATALENGTH ( @sText ) > 1
112 BEGIN
113 SET @value = SUBSTRING ( @sText , 1 , 1 )
114 BEGIN
115 INSERT @retArray (idx, value)
116 VALUES ( @idx , @value )
117 END
118 SET @idx = @idx + 1
119 SET @sText = SUBSTRING ( @sText , 2 , DATALENGTH ( @sText ) - 1 )
120
121 END
122 ELSE
123 BEGIN
124 -- One character remains.
125 -- Insert the character, and exit the WHILE loop.
126 INSERT @retArray (idx, value)
127 VALUES ( @idx , @sText )
128 SET @bcontinue = 0
129 END
130 END
131
132 END
133
134 RETURN
135 END
136
137 GO
138
139 SET QUOTED_IDENTIFIER OFF
140 GO
141 SET ANSI_NULLS ON
142 GO
143
144
145
146 -- -Here is an example usage of the T-SQL SPLIT function in action
147 CREATE FUNCTION SPLIT
148 (
149 @s nvarchar ( max ),
150 @trimPieces bit ,
151 @returnEmptyStrings bit
152 )
153 returns @t table (val nvarchar ( max ))
154 as
155 begin
156
157 declare @i int , @j int
158 select @i = 0 , @j = ( len ( @s ) - len ( replace ( @s , ' , ' , '' )))
159
160 ; with cte
161 as
162 (
163 select
164 i = @i + 1 ,
165 s = @s ,
166 n = substring ( @s , 0 , charindex ( ' , ' , @s )),
167 m = substring ( @s , charindex ( ' , ' , @s ) + 1 , len ( @s ) - charindex ( ' , ' , @s ))
168
169 union all
170
171 select
172 i = cte.i + 1 ,
173 s = cte.m,
174 n = substring (cte.m, 0 , charindex ( ' , ' , cte.m)),
175 m = substring (
176 cte.m,
177 charindex ( ' , ' , cte.m) + 1 ,
178 len (cte.m) - charindex ( ' , ' , cte.m)
179 )
180 from cte
181 where i <= @j
182 )
183 insert into @t (val)
184 select pieces
185 from
186 (
187 select
188 case
189 when @trimPieces = 1
190 then ltrim ( rtrim ( case when i <= @j then n else m end ))
191 else case when i <= @j then n else m end
192 end as pieces
193 from cte
194 ) t
195 where
196 ( @returnEmptyStrings = 0 and len (pieces) > 0 )
197 or ( @returnEmptyStrings = 1 )
198 option (maxrecursion 0 )
199
200 return
201
202 end
203
204 GO
205 -- 测试
206 declare @s nvarchar ( max )
207 select @s = N ' ,,45 , 1, 56, 346 456,8,5, ,d,1,4, 5 9 ,t,,4,5 ,,, w, 3,, '
208 select * from dbo.split( @s , 1 , 0 )
209
210
211 -- -
212 DECLARE @t table (c1 varchar ( 200 ))
213 INSERT INTO @t (c1) VALUES ( ' LongerValue/Place_1/Last_Place ' )
214
215 SELECT site = PARSENAME ( REPLACE (c1, ' / ' , ' . ' ), 3 ),
216 floor = PARSENAME ( REPLACE (c1, ' / ' , ' . ' ), 2 ),
217 location = PARSENAME ( REPLACE (c1, ' / ' , ' . ' ), 1 )
218 FROM @t
219
220
221 CREATE FUNCTION dbo.Split ( @sep char ( 1 ), @s varchar ( 512 ))
222 RETURNS table
223 AS
224 RETURN (
225 WITH Pieces(pn, start, stop) AS (
226 SELECT 1 , 1 , CHARINDEX ( @sep , @s )
227 UNION ALL
228 SELECT pn + 1 , stop + 1 , CHARINDEX ( @sep , @s , stop + 1 )
229 FROM Pieces
230 WHERE stop > 0
231 )
232 SELECT pn,
233 SUBSTRING ( @s , start, CASE WHEN stop > 0 THEN stop - start ELSE 512 END ) AS s
234 FROM Pieces
235 )
236
237
238 -- I use this function (SQL Server 2005 and above).
239 create function [ dbo ] . [ Split ]
240 (
241 @string nvarchar ( 4000 ),
242 @delimiter nvarchar ( 10 )
243 )
244 returns @table table
245 (
246 [ Value ] nvarchar ( 4000 )
247 )
248 begin
249 declare @nextString nvarchar ( 4000 )
250 declare @pos int , @nextPos int
251 declare @commaCheck nvarchar ( 1 )
252
253 set @nextString = ''
254 set @commaCheck = right ( @string , 1 )
255 set @string = @string + @delimiter
256
257 set @pos = charindex ( @delimiter , @string )
258 set @nextPos = 1
259 while ( @pos <> 0 )
260 begin
261 set @nextString = substring ( @string , 1 , @pos - 1 )
262
263 insert into @table
264 (
265 [ Value ]
266 )
267 values
268 (
269 @nextString
270 )
271
272 set @string = substring ( @string , @pos + 1 , len ( @string ))
273 set @nextPos = @pos
274 set @pos = charindex ( @delimiter , @string )
275 end
276 return
277 end
278
279 --
280 create FUNCTION dbo.fn_Split2 ( @sep nvarchar ( 10 ), @s nvarchar ( 4000 ))
281 RETURNS table
282 AS
283 RETURN (
284 WITH Pieces(pn, start, stop) AS (
285 SELECT 1 , 1 , CHARINDEX ( @sep , @s )
286 UNION ALL
287 SELECT pn + 1 , stop + ( datalength ( @sep ) / 2 ), CHARINDEX ( @sep , @s , stop + ( datalength ( @sep ) / 2 ))
288 FROM Pieces
289 WHERE stop > 0
290 )
291 SELECT pn,
292 SUBSTRING ( @s , start, CASE WHEN stop > 0 THEN stop - start ELSE 4000 END ) AS s
293 FROM Pieces
294 )
295 --
296 ALTER Function [ dbo ] . [ SplitStr ] (
297 @txt text
298 )
299 Returns @tmp Table
300 (
301 value varchar ( 127 )
302 )
303 as
304 BEGIN
305 declare @str varchar ( 8000 )
306 , @Beg int
307 , @last int
308 , @size int
309
310 set @size = datalength ( @txt )
311 set @Beg = 1
312
313
314 set @str = substring ( @txt , @Beg , 8000 )
315 IF len ( @str ) < 8000 set @Beg = @size
316 ELSE BEGIN
317 set @last = charindex ( ' , ' , reverse ( @str ))
318 set @str = substring ( @txt , @Beg , 8000 - @last )
319 set @Beg = @Beg + 8000 - @last + 1
320 END
321
322 declare @workingString varchar ( 25 )
323 , @stringindex int
324
325
326
327 while @Beg <= @size Begin
328 WHILE LEN ( @str ) > 0 BEGIN
329 SELECT @StringIndex = CHARINDEX ( ' , ' , @str )
330
331 SELECT
332 @workingString = CASE
333 WHEN @StringIndex > 0 THEN SUBSTRING ( @str , 1 , @StringIndex - 1 )
334 ELSE @str
335 END
336
337 INSERT INTO
338 @tmp (value)
339 VALUES
340 ( cast ( rtrim ( ltrim ( @workingString )) as varchar ( 127 )))
341 SELECT @str = CASE
342 WHEN CHARINDEX ( ' , ' , @str ) > 0 THEN SUBSTRING ( @str , @StringIndex + 1 , LEN ( @str ))
343 ELSE ''
344 END
345 END
346 set @str = substring ( @txt , @Beg , 8000 )
347
348 if @Beg = @size set @Beg = @Beg + 1
349 else IF len ( @str ) < 8000 set @Beg = @size
350 ELSE BEGIN
351 set @last = charindex ( ' , ' , reverse ( @str ))
352 set @str = substring ( @txt , @Beg , 8000 - @last )
353 set @Beg = @Beg + 8000 - @last + 1
354
355 END
356 END
357
358 return
359 END
360 --
361 SELECT substring (commaSeparatedTags, 0 , charindex ( ' , ' ,commaSeparatedTags))
362
363 --
364 CREATE FUNCTION SplitWords( @text varchar ( 8000 ))
365 RETURNS @words TABLE (
366 pos smallint primary key ,
367 value varchar ( 8000 )
368 )
369 AS
370 BEGIN
371 DECLARE
372 @pos smallint ,
373 @i smallint ,
374 @j smallint ,
375 @s varchar ( 8000 )
376
377 SET @pos = 1
378 WHILE @pos <= LEN ( @text )
379 BEGIN
380 SET @i = CHARINDEX ( ' ' , @text , @pos )
381 SET @j = CHARINDEX ( ' , ' , @text , @pos )
382 IF @i > 0 OR @j > 0
383 BEGIN
384 IF @i = 0 OR ( @j > 0 AND @j < @i )
385 SET @i = @j
386
387 IF @i > @pos
388 BEGIN
389 -- @i now holds the earliest delimiter in the string
390 SET @s = SUBSTRING ( @text , @pos , @i - @pos )
391
392 INSERT INTO @words
393 VALUES ( @pos , @s )
394 END
395 SET @pos = @i + 1
396
397 WHILE @pos < LEN ( @text )
398 AND SUBSTRING ( @text , @pos , 1 ) IN ( ' ' , ' , ' )
399 SET @pos = @pos + 1
400 END
401 ELSE
402 BEGIN
403 INSERT INTO @words
404 VALUES ( @pos , SUBSTRING ( @text , @pos , LEN ( @text ) - @pos + 1 ))
405
406 SET @pos = LEN ( @text ) + 1
407 END
408 END
409 RETURN
410 END
411
412
413 select *
414
415 from table1 t1
416
417 where dbo.splitwords(t1.column1)
418
419 like dbo.splitwords( @userinput )
420
421 ( where @userinput is a input parameter)
422
423 -- I think dbo.splitwords(t1.column1) does not work in sql server 2000.
424
425 -- I hope you have solution.
426
427 -- Sample table.
428 CREATE TABLE dbo. [ tblRates ] (
429 [ RateID ] [ id ] ,
430 [ Rate ] [ varchar ] ( 80 ) COLLATE Latin1_General_CI_AS NULL
431 )
432 GO
433
434 -- Sample data.
435 CREATE TABLE tblRates
436 (
437 RateID INT ,
438 Rate VARCHAR ( 100 )
439 )
440 GO
441
442 INSERT INTO tblRates (RateID, Rate)
443 VALUES ( 1 , ' 1.123456789,2.1234567,3.12345 ' )
444
445
446 -- Current query, with the three rates split out (Fields: Rate1,Rate2,Rate3)
447 SELECT
448 Rate AS ' MasterRates '
449 , ' Rate1 ' = LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )
450 , ' Rate2 ' =
451 LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )
452 , ' Rate3 ' = SUBSTRING (Rate, (( LEN ( LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )) + 1 ) +
453 ( LEN ( LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )) + 1 )) + 1
454 , LEN (Rate) )
455
456 -- Fields for calculation reference.
457 , ' Rate1LengthIncDelimitter ' = LEN ( LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )) + 1
458 , ' Rate2LengthIncDelimitter ' =
459 LEN ( LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )) + 1
460 , ' Rate3StartingCharacterPostion ' =
461 (( LEN ( LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )) + 1 ) +
462 ( LEN ( LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )) + 1 )) + 1
463 FROM tblRates
464
465 select RateID,
466 replace ( parsename ( replace ( replace (
467 Rate, ' . ' , ' / ' ), ' , ' , ' . ' ), 3 ), ' / ' , ' . ' ) AS ' A ' ,
468 replace ( parsename ( replace ( replace (
469 Rate, ' . ' , ' / ' ), ' , ' , ' . ' ), 2 ), ' / ' , ' . ' ) AS ' B ' ,
470 replace ( parsename ( replace ( replace (
471 Rate, ' . ' , ' / ' ), ' , ' , ' . ' ), 1 ), ' / ' , ' . ' ) AS ' C '
472 from tblRates
473
474 CREATE FUNCTION [ dbo ] . [ SPLIT ] (
475
476 @str_in VARCHAR ( 8000 ),
477
478 @separator VARCHAR ( 4 ) )
479
480 RETURNS @strtable TABLE (strval VARCHAR ( 8000 ))
481
482 AS
483
484 BEGIN
485
486 DECLARE
487
488 @Occurrences INT ,
489
490 @Counter INT ,
491
492 @tmpStr VARCHAR ( 8000 )
493
494 SET @Counter = 0
495
496 IF SUBSTRING ( @str_in , LEN ( @str_in ), 1 ) <> @separator
497
498 SET @str_in = @str_in + @separator
499
500 SET @Occurrences = ( DATALENGTH ( REPLACE ( @str_in , @separator , @separator + ' # ' )) - DATALENGTH ( @str_in )) / DATALENGTH ( @separator )
501
502 SET @tmpStr = @str_in
503
504 WHILE @Counter <= @Occurrences
505
506 BEGIN
507
508 SET @Counter = @Counter + 1
509
510 INSERT INTO @strtable
511
512 VALUES ( SUBSTRING ( @tmpStr , 1 , CHARINDEX ( @separator , @tmpStr ) - 1 ))
513
514 SET @tmpStr = SUBSTRING ( @tmpStr , CHARINDEX ( @separator , @tmpStr ) + 1 , 8000 )
515
516
517 IF DATALENGTH ( @tmpStr ) = 0
518
519 BREAK
520
521
522 END
523
524 RETURN
525
526 END
527
528 GO
529
530 --
531 CREATE FUNCTION [ dbo ] . [ Split ]
532 (
533 @pvcSearchString VARCHAR ( 8000 ),
534 @pvcSeparator VARCHAR ( 5 )
535 )
536
537 RETURNS @strtable TABLE (strval VARCHAR ( 8000 ))
538
539 AS
540
541 BEGIN
542 DECLARE
543 @tmpStr VARCHAR ( 8000 ),
544 @intSeparatorLength INT
545
546 SET @intSeparatorLength = LEN ( @pvcSeparator )
547
548 SET @tmpStr = @pvcSearchString
549 WHILE 1 = 1
550 BEGIN
551 INSERT INTO @strtable VALUES ( SUBSTRING ( @tmpStr , 0 , CHARINDEX ( @pvcSeparator , @tmpStr )))
552 SET @tmpStr = SUBSTRING ( @tmpStr , CHARINDEX ( @pvcSeparator , @tmpStr ) + LEN ( @pvcSeparator ), 8000 )
553 IF CHARINDEX ( @pvcSeparator , @tmpStr ) < 1
554 BREAK
555 END
556
557 RETURN
558 END
559
560 --
561 /*
562 Sample Delineated Data From [Col1]:
563 "Item 1 | Item 2 | Item 3"
564
565 Desired Target Result:
566 [ColItem1],[ColItem2],[ColItem3]
567 “Item 1”, Item 2”, Item 3”
568
569 Use the MS SQL PARSENAME function to achieve this. It’s a little backwards from how you think it should work, but follow this example to get the basic idea. PARSENAME can by used in similar fashion to a split function.
570
571 Sample Syntax (default delimited style):
572 */
573 Select
574 PARSENAME ( [ Col1 ] , 3 ) as ColItem1
575 , PARSENAME ( [ Col1 ] , 2 ) as ColItem2
576 , PARSENAME ( [ Col1 ] , 1 ) as ColItem3
577 From [ myTable ]
578
579 -- Sample Syntax (dealing with the pipe | ):
580
581 Select
582 PARSENAME ( replace ( [ Col1 ] , ' | ' , ' . ' ), 3 ) as ColItem1
583 , PARSENAME ( replace ( [ Col1 ] , ' | ' , ' . ' ), 2 ) as ColItem2
584 , PARSENAME ( replace ( [ Col1 ] , ' | ' , ' . ' ), 1 ) as ColItem3
585 From [ myTable ]
586
587 -- -split column with part of text string
588 CREATE TABLE myTable99(Col1 varchar ( 255 ))
589 GO
590
591 SET NOCOUNT ON
592 INSERT INTO myTable99(Col1)
593 SELECT ' name: johnson email: firstname: philip need: doc 12 ' UNION ALL
594 SELECT ' name: johnson email: pjohnson@yahoo.com firstname: philip need: doc 13 '
595 GO
596
597 CREATE FUNCTION udf_GetString( @Col1 varchar ( 255 ), @tag varchar ( 255 ))
598 RETURNS varchar ( 255 )
599 AS
600 BEGIN
601 DECLARE @str varchar ( 255 )
602 SELECT @str = SUBSTRING ( @Col1
603 , CHARINDEX ( @tag , @Col1 ) + 1 + LEN ( @tag )
604 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag , @Col1 ) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag , @Col1 ) + 1 + LEN ( @tag )))
605 FROM myTable99
606 RETURN @str
607 END
608 GO
609
610 SELECT dbo.udf_GetString(Col1, ' firstname: ' )
611 , dbo.udf_GetString(Col1, ' email: ' )
612 , dbo.udf_GetString(Col1, ' need: ' )
613 , Col1
614 FROM myTable99
615 GO
616
617 DECLARE @tag varchar ( 255 )
618 SELECT @tag = ' firstname: '
619 SELECT SUBSTRING (Col1
620 , CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )
621 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )))
622 FROM myTable99
623 SELECT @tag = ' email: '
624 SELECT SUBSTRING (Col1
625 , CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )
626 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )))
627 FROM myTable99
628 SELECT @tag = ' need: '
629 SELECT SUBSTRING (Col1
630 , CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )
631 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )))
632 FROM myTable99
633 GO
634
635
636 SET NOCOUNT OFF
637 DROP FUNCTION udf_GetString
638 DROP TABLE myTable99
639 GO
640
641 SELECT * FROM [ dbo ] . [ SPLIT ] ( ' 1,2,3 ' )
642
643
644 CREATE FUNCTION [ dbo ] . [ SPLIT ]
645 (
646 @Text TEXT
647 )
648 RETURNS @output TABLE (
649 Item INT
650 )
651 BEGIN
652
653 DECLARE @start INT , @end INT , @Datalen INT
654
655
656 SELECT @start = 1 ,
657 @end = CHARINDEX ( ' , ' , @Text ),
658 @Datalen = DATALENGTH ( @Text ) + 1
659
660 WHILE @start < @Datalen BEGIN
661 IF @end <= @start BEGIN
662 SET @end = @Datalen
663 END
664
665 INSERT INTO @output (Item)
666 VALUES ( SUBSTRING ( @Text , @start , @end - @start ))
667
668 SET @start = @end + 1
669 SET @end = CHARINDEX ( ' , ' , SUBSTRING ( @Text , @start , 20 )) + @start - 1
670 END
671 RETURN
672 END
673
674
675
676 And here get 2 fields
677
678 SELECT * FROM [ dbo ] . [ SPLIT_2 ] ( ' 1_1,2_1,2_2 ' )
679
680 CREATE FUNCTION [ dbo ] . [ SPLIT_2 ]
681 (
682 @Text TEXT
683 )
684 RETURNS @output TABLE (
685 Document_id INT ,IndexOf int
686 )
687 BEGIN
688
689 DECLARE @start INT , @end INT , @Datalen INT ,
690 @Values VARCHAR ( 50 ), @Document VARCHAR ( 5 ),
691 @Index VARCHAR ( 5 ), @CharIndex INT
692
693 SELECT @start = 1 ,
694 @end = CHARINDEX ( ' , ' , @Text ),
695 @Datalen = DATALENGTH ( @Text ) + 1
696
697 WHILE @start < @Datalen BEGIN
698 IF @end <= @start BEGIN
699 SET @end = @Datalen
700 END
701
702 SET @Values = SUBSTRING ( @Text , @start , @end - @start )
703 SET @CharIndex = CHARINDEX ( ' _ ' , @Values )
704
705 SET @Document = SUBSTRING ( @Values , 0 , @CharIndex )
706 SET @Index = SUBSTRING ( @Values , @CharIndex + 1 , 5 )
707
708 INSERT INTO @output (document_id , IndexOf)
709 VALUES ( @Document , @Index )
710
711 SET @start = @end + 1
712 SET @end = CHARINDEX ( ' , ' , SUBSTRING ( @Text , @start , 20 )) + @start - 1
713 END
714 RETURN
715
716
717 -- -SQL: String Split Function
718 CREATE FUNCTION Split( @String varchar ( 4000 ), @Delimiter char ( 1 ))
719 RETURNS @Results TABLE (ID int , Items nvarchar ( 4000 ))
720 AS
721
722 BEGIN
723 DECLARE @INDEX INT
724 DECLARE @SLICE nvarchar ( 4000 )
725 DECLARE @ID int
726
727 SELECT @INDEX = 1 , @ID = 1
728 WHILE @INDEX != 0
729
730 BEGIN
731 -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
732 SELECT @INDEX = CHARINDEX ( @Delimiter , @STRING )
733 -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
734 IF @INDEX != 0
735 SELECT @SLICE = LEFT ( @STRING , @INDEX - 1 )
736 ELSE
737 SELECT @SLICE = @STRING
738 -- PUT THE ITEM INTO THE RESULTS SET
739 INSERT INTO @Results (ID, Items) VALUES ( @ID , @SLICE )
740 SELECT @ID = @ID + 1
741 -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
742 SELECT @STRING = RIGHT ( @STRING , LEN ( @STRING ) - @INDEX )
743 -- BREAK OUT IF WE ARE DONE
744 IF LEN ( @STRING ) = 0 BREAK
745 END
746 RETURN
747
748 select Items from dbo.Split( @List , ' , ' )
749
750 Create FUNCTION Split( @String varchar ( 4000 ), @Delimiter char ( 1 ))
751 RETURNS @Results TABLE (ID int , Items nvarchar ( 4000 ))
752 AS
753
754 BEGIN
755 DECLARE @INDEX INT
756 DECLARE @SLICE nvarchar ( 4000 )
757 DECLARE @ID int
758
759 SELECT @INDEX = 1 , @ID = 1
760 WHILE @INDEX != 0
761
762 BEGIN
763 -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
764 SELECT @INDEX = CHARINDEX ( @Delimiter , @String )
765 -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
766 IF @INDEX != 0
767 SELECT @SLICE = LEFT ( @String , @INDEX - 1 )
768 ELSE
769 SELECT @SLICE = @String
770 -- PUT THE ITEM INTO THE RESULTS SET
771 INSERT INTO @Results (ID, Items) VALUES ( @ID , @SLICE )
772 SELECT @ID = @ID + 1
773 -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
774 SELECT @String = RIGHT ( @String , LEN ( @String ) - @INDEX )
775 -- BREAK OUT IF WE ARE DONE
776 IF LEN ( @String ) = 0 BREAK
777 END
778 Return
779 END
780
781
2
3 CREATE TABLE inventory (fullname varchar ( 60 ) NOT NULL )
4 go
5 INSERT inventory(fullname)
6 VALUES ( ' 2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094 ' )
7 INSERT inventory(fullname)
8 VALUES ( ' 2004 Inventory ' )
9 INSERT inventory(fullname)
10 VALUES ( ' 2004 Inventory:Ex.Plant Farm1:1st Cut ' )
11 go
12
13 SELECT inventory, plant = nullif (plant, '' ), cut = nullif (cut, '' ),
14 grade = nullif (grade, '' ), lot# = nullif (lot#, '' )
15 FROM
16 ( SELECT inventory, plant, cut,
17 left (rest, charindex ( ' : ' , rest + ' : ' ) - 1 ) AS grade,
18 substring (rest, charindex ( ' : ' , rest + ' : ' ) + 1 ,
19 len (rest)) AS lot#
20 FROM
21 ( SELECT inventory, plant,
22 left (rest, charindex ( ' : ' , rest + ' : ' ) - 1 ) AS cut,
23 substring (rest, charindex ( ' : ' , rest + ' : ' ) + 1 ,
24 len (rest)) AS rest
25 FROM
26 ( SELECT inventory,
27 left (rest, charindex ( ' : ' , rest + ' : ' ) - 1 ) AS plant,
28 substring (rest, charindex ( ' : ' , rest + ' : ' ) + 1 ,
29 len (rest)) AS rest
30 FROM
31 ( SELECT left (fullname, charindex ( ' : ' , fullname + ' : ' ) - 1 ) AS inventory,
32 substring (fullname, charindex ( ' : ' , fullname + ' : ' ) + 1 ,
33 len (fullname)) AS rest
34 FROM inventory) AS a) AS b) AS c) AS d
35 go
36
37 DROP TABLE inventory
38
39 -- Below is a UDF to take a comma delim value and return in table format,
40 -- the split results.. Use like select * from dbo.Split('1,2,3,3',',')
41 CREATE FUNCTION dbo.Split( @sText varchar ( 8000 ), @sDelim varchar ( 20 ) = '' )
42 RETURNS @retArray TABLE (idx smallint Primary Key , value varchar ( 8000 ))
43 AS
44 BEGIN
45 DECLARE @idx int ,
46 @value varchar ( 8000 ),
47 @bcontinue bit ,
48 @iStrike int ,
49 @iDelimlength int
50
51 IF @sDelim = ' Space '
52 BEGIN
53 SET @sDelim = ' '
54 END
55
56 SET @idx = 0
57 SET @sText = LTrim ( RTrim ( @sText ))
58 SET @iDelimlength = DATALENGTH ( @sDelim )
59 SET @bcontinue = 1
60
61 if ( Len ( @sText ) = 0 )
62 return
63
64 IF NOT (( @iDelimlength = 0 ) or ( @sDelim = ' Empty ' ))
65 BEGIN
66 WHILE @bcontinue = 1
67 BEGIN
68
69 -- If you can find the delimiter in the text, retrieve the first element
70 and
71 -- insert it with its index into the return table.
72
73 IF CHARINDEX ( @sDelim , @sText ) > 0
74 BEGIN
75 SET @value = SUBSTRING ( @sText , 1 , CHARINDEX ( @sDelim , @sText ) - 1 )
76 BEGIN
77 INSERT @retArray (idx, value)
78 VALUES ( @idx , @value )
79 END
80
81 -- Trim the element and its delimiter from the front of the string.
82 -- Increment the index and loop.
83 SET @iStrike = DATALENGTH ( @value ) + @iDelimlength
84 SET @idx = @idx + 1
85 SET @sText = LTrim ( Right ( @sText , DATALENGTH ( @sText ) - @iStrike ))
86
87 END
88 ELSE
89 BEGIN
90 -- If you canÆt find the delimiter in the text, @sText is the last
91 value in
92 -- @retArray.
93 SET @value = @sText
94 BEGIN
95 INSERT @retArray (idx, value)
96 VALUES ( @idx , @value )
97 END
98 -- Exit the WHILE loop.
99 SET @bcontinue = 0
100 END
101 END
102 END
103 ELSE
104 BEGIN
105 WHILE @bcontinue = 1
106 BEGIN
107 -- If the delimiter is an empty string, check for remaining text
108 -- instead of a delimiter. Insert the first character into the
109 -- retArray table. Trim the character from the front of the string.
110 -- Increment the index and loop.
111 IF DATALENGTH ( @sText ) > 1
112 BEGIN
113 SET @value = SUBSTRING ( @sText , 1 , 1 )
114 BEGIN
115 INSERT @retArray (idx, value)
116 VALUES ( @idx , @value )
117 END
118 SET @idx = @idx + 1
119 SET @sText = SUBSTRING ( @sText , 2 , DATALENGTH ( @sText ) - 1 )
120
121 END
122 ELSE
123 BEGIN
124 -- One character remains.
125 -- Insert the character, and exit the WHILE loop.
126 INSERT @retArray (idx, value)
127 VALUES ( @idx , @sText )
128 SET @bcontinue = 0
129 END
130 END
131
132 END
133
134 RETURN
135 END
136
137 GO
138
139 SET QUOTED_IDENTIFIER OFF
140 GO
141 SET ANSI_NULLS ON
142 GO
143
144
145
146 -- -Here is an example usage of the T-SQL SPLIT function in action
147 CREATE FUNCTION SPLIT
148 (
149 @s nvarchar ( max ),
150 @trimPieces bit ,
151 @returnEmptyStrings bit
152 )
153 returns @t table (val nvarchar ( max ))
154 as
155 begin
156
157 declare @i int , @j int
158 select @i = 0 , @j = ( len ( @s ) - len ( replace ( @s , ' , ' , '' )))
159
160 ; with cte
161 as
162 (
163 select
164 i = @i + 1 ,
165 s = @s ,
166 n = substring ( @s , 0 , charindex ( ' , ' , @s )),
167 m = substring ( @s , charindex ( ' , ' , @s ) + 1 , len ( @s ) - charindex ( ' , ' , @s ))
168
169 union all
170
171 select
172 i = cte.i + 1 ,
173 s = cte.m,
174 n = substring (cte.m, 0 , charindex ( ' , ' , cte.m)),
175 m = substring (
176 cte.m,
177 charindex ( ' , ' , cte.m) + 1 ,
178 len (cte.m) - charindex ( ' , ' , cte.m)
179 )
180 from cte
181 where i <= @j
182 )
183 insert into @t (val)
184 select pieces
185 from
186 (
187 select
188 case
189 when @trimPieces = 1
190 then ltrim ( rtrim ( case when i <= @j then n else m end ))
191 else case when i <= @j then n else m end
192 end as pieces
193 from cte
194 ) t
195 where
196 ( @returnEmptyStrings = 0 and len (pieces) > 0 )
197 or ( @returnEmptyStrings = 1 )
198 option (maxrecursion 0 )
199
200 return
201
202 end
203
204 GO
205 -- 测试
206 declare @s nvarchar ( max )
207 select @s = N ' ,,45 , 1, 56, 346 456,8,5, ,d,1,4, 5 9 ,t,,4,5 ,,, w, 3,, '
208 select * from dbo.split( @s , 1 , 0 )
209
210
211 -- -
212 DECLARE @t table (c1 varchar ( 200 ))
213 INSERT INTO @t (c1) VALUES ( ' LongerValue/Place_1/Last_Place ' )
214
215 SELECT site = PARSENAME ( REPLACE (c1, ' / ' , ' . ' ), 3 ),
216 floor = PARSENAME ( REPLACE (c1, ' / ' , ' . ' ), 2 ),
217 location = PARSENAME ( REPLACE (c1, ' / ' , ' . ' ), 1 )
218 FROM @t
219
220
221 CREATE FUNCTION dbo.Split ( @sep char ( 1 ), @s varchar ( 512 ))
222 RETURNS table
223 AS
224 RETURN (
225 WITH Pieces(pn, start, stop) AS (
226 SELECT 1 , 1 , CHARINDEX ( @sep , @s )
227 UNION ALL
228 SELECT pn + 1 , stop + 1 , CHARINDEX ( @sep , @s , stop + 1 )
229 FROM Pieces
230 WHERE stop > 0
231 )
232 SELECT pn,
233 SUBSTRING ( @s , start, CASE WHEN stop > 0 THEN stop - start ELSE 512 END ) AS s
234 FROM Pieces
235 )
236
237
238 -- I use this function (SQL Server 2005 and above).
239 create function [ dbo ] . [ Split ]
240 (
241 @string nvarchar ( 4000 ),
242 @delimiter nvarchar ( 10 )
243 )
244 returns @table table
245 (
246 [ Value ] nvarchar ( 4000 )
247 )
248 begin
249 declare @nextString nvarchar ( 4000 )
250 declare @pos int , @nextPos int
251 declare @commaCheck nvarchar ( 1 )
252
253 set @nextString = ''
254 set @commaCheck = right ( @string , 1 )
255 set @string = @string + @delimiter
256
257 set @pos = charindex ( @delimiter , @string )
258 set @nextPos = 1
259 while ( @pos <> 0 )
260 begin
261 set @nextString = substring ( @string , 1 , @pos - 1 )
262
263 insert into @table
264 (
265 [ Value ]
266 )
267 values
268 (
269 @nextString
270 )
271
272 set @string = substring ( @string , @pos + 1 , len ( @string ))
273 set @nextPos = @pos
274 set @pos = charindex ( @delimiter , @string )
275 end
276 return
277 end
278
279 --
280 create FUNCTION dbo.fn_Split2 ( @sep nvarchar ( 10 ), @s nvarchar ( 4000 ))
281 RETURNS table
282 AS
283 RETURN (
284 WITH Pieces(pn, start, stop) AS (
285 SELECT 1 , 1 , CHARINDEX ( @sep , @s )
286 UNION ALL
287 SELECT pn + 1 , stop + ( datalength ( @sep ) / 2 ), CHARINDEX ( @sep , @s , stop + ( datalength ( @sep ) / 2 ))
288 FROM Pieces
289 WHERE stop > 0
290 )
291 SELECT pn,
292 SUBSTRING ( @s , start, CASE WHEN stop > 0 THEN stop - start ELSE 4000 END ) AS s
293 FROM Pieces
294 )
295 --
296 ALTER Function [ dbo ] . [ SplitStr ] (
297 @txt text
298 )
299 Returns @tmp Table
300 (
301 value varchar ( 127 )
302 )
303 as
304 BEGIN
305 declare @str varchar ( 8000 )
306 , @Beg int
307 , @last int
308 , @size int
309
310 set @size = datalength ( @txt )
311 set @Beg = 1
312
313
314 set @str = substring ( @txt , @Beg , 8000 )
315 IF len ( @str ) < 8000 set @Beg = @size
316 ELSE BEGIN
317 set @last = charindex ( ' , ' , reverse ( @str ))
318 set @str = substring ( @txt , @Beg , 8000 - @last )
319 set @Beg = @Beg + 8000 - @last + 1
320 END
321
322 declare @workingString varchar ( 25 )
323 , @stringindex int
324
325
326
327 while @Beg <= @size Begin
328 WHILE LEN ( @str ) > 0 BEGIN
329 SELECT @StringIndex = CHARINDEX ( ' , ' , @str )
330
331 SELECT
332 @workingString = CASE
333 WHEN @StringIndex > 0 THEN SUBSTRING ( @str , 1 , @StringIndex - 1 )
334 ELSE @str
335 END
336
337 INSERT INTO
338 @tmp (value)
339 VALUES
340 ( cast ( rtrim ( ltrim ( @workingString )) as varchar ( 127 )))
341 SELECT @str = CASE
342 WHEN CHARINDEX ( ' , ' , @str ) > 0 THEN SUBSTRING ( @str , @StringIndex + 1 , LEN ( @str ))
343 ELSE ''
344 END
345 END
346 set @str = substring ( @txt , @Beg , 8000 )
347
348 if @Beg = @size set @Beg = @Beg + 1
349 else IF len ( @str ) < 8000 set @Beg = @size
350 ELSE BEGIN
351 set @last = charindex ( ' , ' , reverse ( @str ))
352 set @str = substring ( @txt , @Beg , 8000 - @last )
353 set @Beg = @Beg + 8000 - @last + 1
354
355 END
356 END
357
358 return
359 END
360 --
361 SELECT substring (commaSeparatedTags, 0 , charindex ( ' , ' ,commaSeparatedTags))
362
363 --
364 CREATE FUNCTION SplitWords( @text varchar ( 8000 ))
365 RETURNS @words TABLE (
366 pos smallint primary key ,
367 value varchar ( 8000 )
368 )
369 AS
370 BEGIN
371 DECLARE
372 @pos smallint ,
373 @i smallint ,
374 @j smallint ,
375 @s varchar ( 8000 )
376
377 SET @pos = 1
378 WHILE @pos <= LEN ( @text )
379 BEGIN
380 SET @i = CHARINDEX ( ' ' , @text , @pos )
381 SET @j = CHARINDEX ( ' , ' , @text , @pos )
382 IF @i > 0 OR @j > 0
383 BEGIN
384 IF @i = 0 OR ( @j > 0 AND @j < @i )
385 SET @i = @j
386
387 IF @i > @pos
388 BEGIN
389 -- @i now holds the earliest delimiter in the string
390 SET @s = SUBSTRING ( @text , @pos , @i - @pos )
391
392 INSERT INTO @words
393 VALUES ( @pos , @s )
394 END
395 SET @pos = @i + 1
396
397 WHILE @pos < LEN ( @text )
398 AND SUBSTRING ( @text , @pos , 1 ) IN ( ' ' , ' , ' )
399 SET @pos = @pos + 1
400 END
401 ELSE
402 BEGIN
403 INSERT INTO @words
404 VALUES ( @pos , SUBSTRING ( @text , @pos , LEN ( @text ) - @pos + 1 ))
405
406 SET @pos = LEN ( @text ) + 1
407 END
408 END
409 RETURN
410 END
411
412
413 select *
414
415 from table1 t1
416
417 where dbo.splitwords(t1.column1)
418
419 like dbo.splitwords( @userinput )
420
421 ( where @userinput is a input parameter)
422
423 -- I think dbo.splitwords(t1.column1) does not work in sql server 2000.
424
425 -- I hope you have solution.
426
427 -- Sample table.
428 CREATE TABLE dbo. [ tblRates ] (
429 [ RateID ] [ id ] ,
430 [ Rate ] [ varchar ] ( 80 ) COLLATE Latin1_General_CI_AS NULL
431 )
432 GO
433
434 -- Sample data.
435 CREATE TABLE tblRates
436 (
437 RateID INT ,
438 Rate VARCHAR ( 100 )
439 )
440 GO
441
442 INSERT INTO tblRates (RateID, Rate)
443 VALUES ( 1 , ' 1.123456789,2.1234567,3.12345 ' )
444
445
446 -- Current query, with the three rates split out (Fields: Rate1,Rate2,Rate3)
447 SELECT
448 Rate AS ' MasterRates '
449 , ' Rate1 ' = LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )
450 , ' Rate2 ' =
451 LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )
452 , ' Rate3 ' = SUBSTRING (Rate, (( LEN ( LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )) + 1 ) +
453 ( LEN ( LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )) + 1 )) + 1
454 , LEN (Rate) )
455
456 -- Fields for calculation reference.
457 , ' Rate1LengthIncDelimitter ' = LEN ( LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )) + 1
458 , ' Rate2LengthIncDelimitter ' =
459 LEN ( LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )) + 1
460 , ' Rate3StartingCharacterPostion ' =
461 (( LEN ( LEFT (Rate, CHARINDEX ( ' , ' ,Rate) - 1 )) + 1 ) +
462 ( LEN ( LEFT ( SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate)), CHARINDEX ( ' , ' , SUBSTRING (Rate,( CHARINDEX ( ' , ' ,Rate) + 1 ), LEN (Rate))) - 1 )) + 1 )) + 1
463 FROM tblRates
464
465 select RateID,
466 replace ( parsename ( replace ( replace (
467 Rate, ' . ' , ' / ' ), ' , ' , ' . ' ), 3 ), ' / ' , ' . ' ) AS ' A ' ,
468 replace ( parsename ( replace ( replace (
469 Rate, ' . ' , ' / ' ), ' , ' , ' . ' ), 2 ), ' / ' , ' . ' ) AS ' B ' ,
470 replace ( parsename ( replace ( replace (
471 Rate, ' . ' , ' / ' ), ' , ' , ' . ' ), 1 ), ' / ' , ' . ' ) AS ' C '
472 from tblRates
473
474 CREATE FUNCTION [ dbo ] . [ SPLIT ] (
475
476 @str_in VARCHAR ( 8000 ),
477
478 @separator VARCHAR ( 4 ) )
479
480 RETURNS @strtable TABLE (strval VARCHAR ( 8000 ))
481
482 AS
483
484 BEGIN
485
486 DECLARE
487
488 @Occurrences INT ,
489
490 @Counter INT ,
491
492 @tmpStr VARCHAR ( 8000 )
493
494 SET @Counter = 0
495
496 IF SUBSTRING ( @str_in , LEN ( @str_in ), 1 ) <> @separator
497
498 SET @str_in = @str_in + @separator
499
500 SET @Occurrences = ( DATALENGTH ( REPLACE ( @str_in , @separator , @separator + ' # ' )) - DATALENGTH ( @str_in )) / DATALENGTH ( @separator )
501
502 SET @tmpStr = @str_in
503
504 WHILE @Counter <= @Occurrences
505
506 BEGIN
507
508 SET @Counter = @Counter + 1
509
510 INSERT INTO @strtable
511
512 VALUES ( SUBSTRING ( @tmpStr , 1 , CHARINDEX ( @separator , @tmpStr ) - 1 ))
513
514 SET @tmpStr = SUBSTRING ( @tmpStr , CHARINDEX ( @separator , @tmpStr ) + 1 , 8000 )
515
516
517 IF DATALENGTH ( @tmpStr ) = 0
518
519 BREAK
520
521
522 END
523
524 RETURN
525
526 END
527
528 GO
529
530 --
531 CREATE FUNCTION [ dbo ] . [ Split ]
532 (
533 @pvcSearchString VARCHAR ( 8000 ),
534 @pvcSeparator VARCHAR ( 5 )
535 )
536
537 RETURNS @strtable TABLE (strval VARCHAR ( 8000 ))
538
539 AS
540
541 BEGIN
542 DECLARE
543 @tmpStr VARCHAR ( 8000 ),
544 @intSeparatorLength INT
545
546 SET @intSeparatorLength = LEN ( @pvcSeparator )
547
548 SET @tmpStr = @pvcSearchString
549 WHILE 1 = 1
550 BEGIN
551 INSERT INTO @strtable VALUES ( SUBSTRING ( @tmpStr , 0 , CHARINDEX ( @pvcSeparator , @tmpStr )))
552 SET @tmpStr = SUBSTRING ( @tmpStr , CHARINDEX ( @pvcSeparator , @tmpStr ) + LEN ( @pvcSeparator ), 8000 )
553 IF CHARINDEX ( @pvcSeparator , @tmpStr ) < 1
554 BREAK
555 END
556
557 RETURN
558 END
559
560 --
561 /*
562 Sample Delineated Data From [Col1]:
563 "Item 1 | Item 2 | Item 3"
564
565 Desired Target Result:
566 [ColItem1],[ColItem2],[ColItem3]
567 “Item 1”, Item 2”, Item 3”
568
569 Use the MS SQL PARSENAME function to achieve this. It’s a little backwards from how you think it should work, but follow this example to get the basic idea. PARSENAME can by used in similar fashion to a split function.
570
571 Sample Syntax (default delimited style):
572 */
573 Select
574 PARSENAME ( [ Col1 ] , 3 ) as ColItem1
575 , PARSENAME ( [ Col1 ] , 2 ) as ColItem2
576 , PARSENAME ( [ Col1 ] , 1 ) as ColItem3
577 From [ myTable ]
578
579 -- Sample Syntax (dealing with the pipe | ):
580
581 Select
582 PARSENAME ( replace ( [ Col1 ] , ' | ' , ' . ' ), 3 ) as ColItem1
583 , PARSENAME ( replace ( [ Col1 ] , ' | ' , ' . ' ), 2 ) as ColItem2
584 , PARSENAME ( replace ( [ Col1 ] , ' | ' , ' . ' ), 1 ) as ColItem3
585 From [ myTable ]
586
587 -- -split column with part of text string
588 CREATE TABLE myTable99(Col1 varchar ( 255 ))
589 GO
590
591 SET NOCOUNT ON
592 INSERT INTO myTable99(Col1)
593 SELECT ' name: johnson email: firstname: philip need: doc 12 ' UNION ALL
594 SELECT ' name: johnson email: pjohnson@yahoo.com firstname: philip need: doc 13 '
595 GO
596
597 CREATE FUNCTION udf_GetString( @Col1 varchar ( 255 ), @tag varchar ( 255 ))
598 RETURNS varchar ( 255 )
599 AS
600 BEGIN
601 DECLARE @str varchar ( 255 )
602 SELECT @str = SUBSTRING ( @Col1
603 , CHARINDEX ( @tag , @Col1 ) + 1 + LEN ( @tag )
604 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag , @Col1 ) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag , @Col1 ) + 1 + LEN ( @tag )))
605 FROM myTable99
606 RETURN @str
607 END
608 GO
609
610 SELECT dbo.udf_GetString(Col1, ' firstname: ' )
611 , dbo.udf_GetString(Col1, ' email: ' )
612 , dbo.udf_GetString(Col1, ' need: ' )
613 , Col1
614 FROM myTable99
615 GO
616
617 DECLARE @tag varchar ( 255 )
618 SELECT @tag = ' firstname: '
619 SELECT SUBSTRING (Col1
620 , CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )
621 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )))
622 FROM myTable99
623 SELECT @tag = ' email: '
624 SELECT SUBSTRING (Col1
625 , CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )
626 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )))
627 FROM myTable99
628 SELECT @tag = ' need: '
629 SELECT SUBSTRING (Col1
630 , CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )
631 , CHARINDEX ( ' ' ,Col1, CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )) - ( CHARINDEX ( @tag ,Col1) + 1 + LEN ( @tag )))
632 FROM myTable99
633 GO
634
635
636 SET NOCOUNT OFF
637 DROP FUNCTION udf_GetString
638 DROP TABLE myTable99
639 GO
640
641 SELECT * FROM [ dbo ] . [ SPLIT ] ( ' 1,2,3 ' )
642
643
644 CREATE FUNCTION [ dbo ] . [ SPLIT ]
645 (
646 @Text TEXT
647 )
648 RETURNS @output TABLE (
649 Item INT
650 )
651 BEGIN
652
653 DECLARE @start INT , @end INT , @Datalen INT
654
655
656 SELECT @start = 1 ,
657 @end = CHARINDEX ( ' , ' , @Text ),
658 @Datalen = DATALENGTH ( @Text ) + 1
659
660 WHILE @start < @Datalen BEGIN
661 IF @end <= @start BEGIN
662 SET @end = @Datalen
663 END
664
665 INSERT INTO @output (Item)
666 VALUES ( SUBSTRING ( @Text , @start , @end - @start ))
667
668 SET @start = @end + 1
669 SET @end = CHARINDEX ( ' , ' , SUBSTRING ( @Text , @start , 20 )) + @start - 1
670 END
671 RETURN
672 END
673
674
675
676 And here get 2 fields
677
678 SELECT * FROM [ dbo ] . [ SPLIT_2 ] ( ' 1_1,2_1,2_2 ' )
679
680 CREATE FUNCTION [ dbo ] . [ SPLIT_2 ]
681 (
682 @Text TEXT
683 )
684 RETURNS @output TABLE (
685 Document_id INT ,IndexOf int
686 )
687 BEGIN
688
689 DECLARE @start INT , @end INT , @Datalen INT ,
690 @Values VARCHAR ( 50 ), @Document VARCHAR ( 5 ),
691 @Index VARCHAR ( 5 ), @CharIndex INT
692
693 SELECT @start = 1 ,
694 @end = CHARINDEX ( ' , ' , @Text ),
695 @Datalen = DATALENGTH ( @Text ) + 1
696
697 WHILE @start < @Datalen BEGIN
698 IF @end <= @start BEGIN
699 SET @end = @Datalen
700 END
701
702 SET @Values = SUBSTRING ( @Text , @start , @end - @start )
703 SET @CharIndex = CHARINDEX ( ' _ ' , @Values )
704
705 SET @Document = SUBSTRING ( @Values , 0 , @CharIndex )
706 SET @Index = SUBSTRING ( @Values , @CharIndex + 1 , 5 )
707
708 INSERT INTO @output (document_id , IndexOf)
709 VALUES ( @Document , @Index )
710
711 SET @start = @end + 1
712 SET @end = CHARINDEX ( ' , ' , SUBSTRING ( @Text , @start , 20 )) + @start - 1
713 END
714 RETURN
715
716
717 -- -SQL: String Split Function
718 CREATE FUNCTION Split( @String varchar ( 4000 ), @Delimiter char ( 1 ))
719 RETURNS @Results TABLE (ID int , Items nvarchar ( 4000 ))
720 AS
721
722 BEGIN
723 DECLARE @INDEX INT
724 DECLARE @SLICE nvarchar ( 4000 )
725 DECLARE @ID int
726
727 SELECT @INDEX = 1 , @ID = 1
728 WHILE @INDEX != 0
729
730 BEGIN
731 -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
732 SELECT @INDEX = CHARINDEX ( @Delimiter , @STRING )
733 -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
734 IF @INDEX != 0
735 SELECT @SLICE = LEFT ( @STRING , @INDEX - 1 )
736 ELSE
737 SELECT @SLICE = @STRING
738 -- PUT THE ITEM INTO THE RESULTS SET
739 INSERT INTO @Results (ID, Items) VALUES ( @ID , @SLICE )
740 SELECT @ID = @ID + 1
741 -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
742 SELECT @STRING = RIGHT ( @STRING , LEN ( @STRING ) - @INDEX )
743 -- BREAK OUT IF WE ARE DONE
744 IF LEN ( @STRING ) = 0 BREAK
745 END
746 RETURN
747
748 select Items from dbo.Split( @List , ' , ' )
749
750 Create FUNCTION Split( @String varchar ( 4000 ), @Delimiter char ( 1 ))
751 RETURNS @Results TABLE (ID int , Items nvarchar ( 4000 ))
752 AS
753
754 BEGIN
755 DECLARE @INDEX INT
756 DECLARE @SLICE nvarchar ( 4000 )
757 DECLARE @ID int
758
759 SELECT @INDEX = 1 , @ID = 1
760 WHILE @INDEX != 0
761
762 BEGIN
763 -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
764 SELECT @INDEX = CHARINDEX ( @Delimiter , @String )
765 -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
766 IF @INDEX != 0
767 SELECT @SLICE = LEFT ( @String , @INDEX - 1 )
768 ELSE
769 SELECT @SLICE = @String
770 -- PUT THE ITEM INTO THE RESULTS SET
771 INSERT INTO @Results (ID, Items) VALUES ( @ID , @SLICE )
772 SELECT @ID = @ID + 1
773 -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
774 SELECT @String = RIGHT ( @String , LEN ( @String ) - @INDEX )
775 -- BREAK OUT IF WE ARE DONE
776 IF LEN ( @String ) = 0 BREAK
777 END
778 Return
779 END
780
781