'把这个字符串分解成各个元素

作者: www.9159.com  发布:2019-11-23

如果有一个字符串 eg: "sun,star,moon,clouds",想要在MS SQL中根据给定的分隔符','把这个字符串分解成各个元素[sun] [star] [moon] [clouds],如何实现呢?为此,创建一个Function,代码如下: 复制代码 代码如下: CREATE FUNCTION [dbo].[Split_StrByDelimiter], @Delimiter CHAR RETURNS @temptable TABLE AS BEGIN DECLARE @idx INT DECLARE @slice VARCHAR SELECT @idx = 1 IF len<1 OR @String IS NULL RETURN while @idx!= 0 BEGIN SET @idx = charindex IF @idx!=0 SET @slice = LEFT ELSE SET @slice = @String IF INSERT INTO @temptable SET @String = RIGHT - @idx) IF len = 0 break END RETURN END 示例:如果输入 SELECT * FROM dbo.Split_StrByDelimiter('sun,star,moon,clouds',',') 结果返回 sun star moon clouds 在上面的代码做变形,返回有多少个元素 复制代码 代码如下: CREATE FUNCTION [dbo].[GetCount_Split_StrByDelimiter], @Delimiter CHAR RETURNS INT AS BEGIN DECLARE @temptable TABLE DECLARE @SplitCount INT DECLARE @idx INT DECLARE @slice VARCHAR SELECT @idx = 1 IF len<1 OR @String IS NULL RETURN 0 while @idx!= 0 BEGIN SET @idx = charindex IF @idx!=0 SET @slice = LEFT ELSE SET @slice = @String IF INSERT INTO @temptable SET @String = RIGHT - @idx) IF len = 0 break END SET @SplitCount= FROM @temptable) RETURN @SplitCount END 示例 SELECT dbo.GetCount_Split_StrByDelimiter('sun,star,moon,clouds',',') 结果返回 4

  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 

/****** Object: UserDefinedFunction [dbo].[GetDirectoryPath] Script Date: 2016-12-16 16:54:05 ******/

SET @ReversedPath = REVERSE(@Path)
SELECT @PathLength = CHARINDEX('/', @ReversedPath)
SELECT @FileName = LEFT(@Path, LEN(@Path) - @PathLength)
RETURN @FileName
END
GO
/****** Object: UserDefinedFunction [dbo].[GetExtension] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetExtension]
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @ExtLength INT

SET QUOTED_IDENTIFIER ON
GO

/****** Object: UserDefinedFunction [dbo].[RepEmpty] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[RepEmpty] (@sourcedata varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN

return isnull(rtrim(ltrim(replace(replace(replace(replace(@sourcedata,char(9),''),char(10),''),char(13),''),'',''))),'')

GO

SET @ReversedPath = REVERSE(@Path)
SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
SELECT @FileName = (case when CHARINDEX('/', @ReversedPath)>0 then RIGHT(@Path, CHARINDEX('/', @ReversedPath)-1) else @Path end)
SELECT @FileName = LEFT(@FileName, LEN(@FileName) - @ExtLength)
RETURN @FileName
END
GO

CREATE FUNCTION [dbo].[GetFileNameWithoutExtension]
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @ExtLength INT

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetDirectoryPath]
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)
DECLARE @PathLength INT

SET @ReversedPath = REVERSE(@Path)
SELECT @FileName = RIGHT(@Path, CHARINDEX('/', @ReversedPath)-1)

GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@Long_str varchar(8000),@split_str varchar(100))
returns @t table(id int,item varchar(20))
as
begin
declare @id int
set @id=1
while(charindex(@split_str,@Long_str) <> 0)
begin
insert @t(id,item) values(@id,substring(@Long_str,1,charindex(@split_str,@Long_str)-1))
set @Long_str = stuff(@Long_str,1,charindex(@split_str,@Long_str), ' ')
set @id=@id+1
end
insert @t(id,item) values (@id,@Long_str)
return
end

SET @ReversedPath = REVERSE(@Path)
SET @FileName = ''
SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
IF (@ExtLength > 0)
BEGIN
SELECT @FileName = RIGHT(@Path, @ExtLength - 1)
END
RETURN @FileName
END
GO
/****** Object: UserDefinedFunction [dbo].[GetFileName] Script Date: 2016-12-16 16:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetFileName]
(
@Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @FileName NVARCHAR(MAX)
DECLARE @ReversedPath NVARCHAR(MAX)

RETURN @FileName
END
GO

/****** Object: UserDefinedFunction [dbo].[GetFileNameWithoutExtension] Script Date: 2016-12-16 17:32:06 ******/
SET ANSI_NULLS ON
GO

END

本文由9159.com发布于www.9159.com,转载请注明出处:'把这个字符串分解成各个元素

关键词: