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

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

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

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

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

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

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

一、按指定符号分割字符串,返回分割后的元素个数
 ALTER FUNCTION [dbo].[Fun_GetStrArrayLength]
(
@originalStr VARCHAR(1024), --要分割的字符串
@split VARCHAR(10) --分隔符号
)
RETURNS INT
AS
BEGIN
DECLARE @location INT; --定义起始位置
DECLARE @start INT; --定义从第几个开始
DECLARE @length INT; --定义变量,用于接收计算元素的个数 SET @originalStr = LTRIM(RTRIM(@originalStr)); --去除字符串左右两侧的空格 SET @location = CHARINDEX(@split, @originalStr); --分割符号在字符串中第一次出现的位置(索引从1开始计数) SET @length = 1; WHILE @location <> 0
BEGIN
SET @start = @location + 1;
SET @location = CHARINDEX(@split, @originalStr, @start);
SET @length = @length + 1;
END
RETURN @length;
END

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

结果:5

二、按指定符号分割字符串,返回分割后指定索引的第几个元素,像数组一样方便
 ALTER FUNCTION [dbo].[Fun_GetStrArrayStrOfIndex]
(
@originalStr VARCHAR(1024), --要分割的字符串
@split VARCHAR(10), --分隔符号
@index INT --取第几个元素
)
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @location INT; --定义第一次出现分隔符号的位置
DECLARE @start INT; --定义开始位置
DECLARE @next INT; --定义下一个位置
DECLARE @seed INT; --定义分割符号的长度 SET @originalStr = LTRIM(RTRIM(@originalStr)); --去除字符串左右2侧空格
SET @start = 1;
SET @next = 1;
SET @seed = LEN(@split); SET @location = CHARINDEX(@split, @originalStr); --第一次出现分隔符号的位置 WHILE @location <> 0
AND @index > @next
BEGIN
SET @start = @location + @seed;
SET @location = CHARINDEX(@split, @originalStr, @start);
SET @next = @next + 1;
END IF @location = 0
BEGIN
SELECT @location = LEN(@originalStr) + 1;
END --存在两种情况:
--1、字符串不存在分隔符号。
--2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。 RETURN SUBSTRING(@originalStr, @start, @location -@start);
END

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

结果:7234

三、像数组一样遍历字符串中的元素
 ALTER FUNCTION [dbo].[Fun_SplitStr]
(
@originalStr VARCHAR(8000), --要分割的字符串
@split varchar(100) --分隔符号
)
RETURNS @temp TABLE(Result VARCHAR(100))
AS
BEGIN
DECLARE @result AS VARCHAR(100); --定义变量用于接收单个结果 SET @originalStr = @originalStr + @split ; WHILE (@originalStr <> '')
BEGIN
SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr, 1) -1) ; INSERT @temp VALUES(@result) ; --STUFF()函数用于删除指定长度的字符,并可以在指定的起点处插入另一组字符。
SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr, 1), '');
END
RETURN
END

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

7

5007

7234

7

上一篇:NPOI 操作Excel


下一篇:Oracle12c 性能优化攻略:攻略1-1:创建具有最优性能的数据库