首页 > 代码库 > SQL Server中自定义函数:用指定的分隔符号分割字符串

SQL Server中自定义函数:用指定的分隔符号分割字符串

2014-11-13 

  微软SQL Server数据库中包含了很多内置的函数,入下图:

它们用于处理日期、数学、元数据、字符串等。

  其中最为常用的就是处理字符串,里面包含了CharIndex()等函数,非常方便使用。

但是对于 特殊字符串的处理,比如:ISBN号 ‘978-7-5007-7234-7‘,如果想获取第三个与第四个分割符号之间的数字,

那么SQL 内置函数无法直接做到。这时就需要自定义函数。下面自定义三个函数,用于处理特殊的字符串。

  一、按指定符号分割字符串,返回分割后的元素个数

 1 ALTER FUNCTION [dbo].[Fun_GetStrArrayLength] 2 ( 3     @originalStr VARCHAR(1024),    --要分割的字符串 4     @split       VARCHAR(10) --分隔符号 5 ) 6 RETURNS INT 7 AS 8 BEGIN 9     DECLARE @location INT;--定义起始位置10     DECLARE @start INT; --定义从第几个开始11     DECLARE @length INT;--定义变量,用于接收计算元素的个数12     13     SET @originalStr = LTRIM(RTRIM(@originalStr)); --去除字符串左右两侧的空格14     15     SET @location = CHARINDEX(@split, @originalStr); --分割符号在字符串中第一次出现的位置(索引从1开始计数)16     17     SET @length = 1;18     19     WHILE @location <> 020     BEGIN21         SET @start = @location + 1;22         SET @location = CHARINDEX(@split, @originalStr, @start);23         SET @length = @length + 1;24     END25     RETURN @length;26 END
View Code

   调用函数:select dbo.Fun_GetStrArrayLength(‘978-7-5007-7234-7‘,‘-‘)

        结果:5

 

  二、按指定符号分割字符串,返回分割后指定索引的第几个元素,像数组一样方便

 1 ALTER FUNCTION [dbo].[Fun_GetStrArrayStrOfIndex] 2 ( 3     @originalStr     VARCHAR(1024),    --要分割的字符串 4     @split           VARCHAR(10),    --分隔符号 5     @index           INT --取第几个元素 6 ) 7 RETURNS VARCHAR(1024) 8 AS 9 BEGIN10     DECLARE @location INT; --定义第一次出现分隔符号的位置11     DECLARE @start INT;--定义开始位置12     DECLARE @next INT; --定义下一个位置13     DECLARE @seed INT; --定义分割符号的长度14     15     SET @originalStr = LTRIM(RTRIM(@originalStr));--去除字符串左右2侧空格16     SET @start = 1;17     SET @next = 1;18     SET @seed = LEN(@split);19     20     SET @location = CHARINDEX(@split, @originalStr);--第一次出现分隔符号的位置21     22     WHILE @location <> 023           AND @index > @next24     BEGIN25         SET @start = @location + @seed;26         SET @location = CHARINDEX(@split, @originalStr, @start);27         SET @next = @next + 1;28     END29     30     IF @location = 031         BEGIN32             SELECT @location = LEN(@originalStr) + 1;33         END34 35     --存在两种情况:36     --1、字符串不存在分隔符号。37     --2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。38     39     RETURN SUBSTRING(@originalStr, @start, @location -@start);40 END
View Code

  调用函数:select dbo.Fun_GetStrArrayStrOfIndex(‘978-7-5007-7234-7‘,4)

           结果:7234

 

  三、像数组一样遍历字符串中的元素

 1 ALTER FUNCTION [dbo].[Fun_SplitStr] 2 ( 3     @originalStr      VARCHAR(8000), --要分割的字符串 4     @split varchar(100)  --分隔符号 5 ) 6 RETURNS @temp TABLE(Result VARCHAR(100)) 7 AS 8 BEGIN 9     DECLARE @result AS VARCHAR(100);   --定义变量用于接收单个结果 10     11     SET @originalStr = @originalStr + @split ;  12       13     WHILE (@originalStr <> ‘‘)14     BEGIN15         SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr, 1) -1) ;  16         17         INSERT @temp VALUES(@result) ;  18         19         --STUFF()函数用于删除指定长度的字符,并可以在指定的起点处插入另一组字符。20         SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr, 1), ‘‘);21     END 22     RETURN23 END   
View Code

  调用示例:select * from dbo.Fun_SplitStr(‘978-7-5007-7234-7‘,‘-‘)
    结果: 978

                     7

                     5007

                     7234

                     7

SQL Server中自定义函数:用指定的分隔符号分割字符串