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

微软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 <> 0
20     BEGIN
21         SET @start = @location + 1;
22         SET @location = CHARINDEX(@split, @originalStr, @start);
23         SET @length = @length + 1;
24     END
25     RETURN @length;
26 END

调用函数: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 BEGIN
10     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 <> 0
23           AND @index > @next
24     BEGIN
25         SET @start = @location + @seed;
26         SET @location = CHARINDEX(@split, @originalStr, @start);
27         SET @next = @next + 1;
28     END
29     
30     IF @location = 0
31         BEGIN
32             SELECT @location = LEN(@originalStr) + 1;
33         END
34 
35     --存在两种情况:
36     --1、字符串不存在分隔符号。
37     --2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
38     
39     RETURN SUBSTRING(@originalStr, @start, @location -@start);
40 END

调用函数: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     BEGIN
15         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     RETURN
23 END

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

            7

           5007

           7234

           7

 

成在管理,败在经验;嬴在选择,输在不学!  贵在坚持!

 

欢迎关注作者头条号 张传宁IT讲堂,获取更多IT文章、视频等优质内容。

   

 个人作品

   

    1、BIMFace.Community.SDK.NET

     开源地址:https://gitee.com/NAlps/BIMFace.SDK

     系列博客:https://www.cnblogs.com/SavionZhang/p/11424431.html

     系列视频:https://www.cnblogs.com/SavionZhang/p/14258393.html

   

    2、ZCN.NET.Common

     开源地址:https://gitee.com/NAlps/zcn.net.common

 技术栈

   

 1、Visual Studio、.C#/.NET、.NET Core、MVC、Web API、RESTful API、gRPC、SignalR、Python

 2、jQuery、Vue.js、Bootstrap

 3、数据库:SQLServer、MySQL、PostgreSQL、Oracle、SQLite、Redis、MongoDB、ElasticSearch、TiDB、达梦DM、人大金仓、 神通、南大通用 GBase、华为 GaussDB 、腾讯 TDSQL 、阿里 PolarDB、蚂蚁金服 OceanBase、东软 OpenBASE、浪潮云溪数据库 ZNBase

 4、ORM:Dapper、Entity Framework、FreeSql、SqlSugar、分库分表、读写分离

 5、架构:领域驱动设计 DDD、ABP

 6、环境:跨平台、Windows、Linux(CentOS、麒麟、统信UOS、深度Linux)、maxOS、IIS、Nginx、Apach

 7、移动App:Android、IOS、HarmonyOS、微信、小程序、快应用、Xamarin、uni-app、MUI、Flutter、Framework7、Cordova、Ionic、React Native、Taro、NutUI、Smobiler

   

 云原生、微服务、Docker、CI/CD、DevOps、K8S;

 Dapr、RabbitMQ、Kafka、分布式、大数据、高并发、负载均衡、中间件、RPC、ELK;

 .NET + Docker + jenkins + Github + Harbor + K8S;

出处:www.cnblogs.com/SavionZhang

作者:张传宁   微软MCP、系统架构设计师、系统集成项目管理工程师、科技部创新工程师。

          专注于微软.NET技术(.NET Core、Web、MVC、WinForm、WPF)、通用权限管理系统、工作流引擎、自动化项目(代码)生成器、SOA 、DDD、 云原生(Docker、微服务、DevOps、CI/CD);PDF、CAD、BIM 审图等研究与应用。

          多次参与电子政务、图书教育、生产制造等企业级大型项目研发与管理工作。

          熟悉中小企业软件开发过程:需求分析、架构设计、编码测试、实施部署、项目管理。通过技术与管理帮助中小企业快速化实现互联网技术全流程解决方案。

         


         

本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

如有问题,可以通过邮件905442693@qq.com联系。共同交流、互相学习。

如果您觉得文章对您有帮助,请点击文章右下角【推荐】。您的鼓励是作者持续创作的最大动力!

posted @ 2014-11-13 11:23  张传宁  阅读(15377)  评论(3编辑  收藏  举报
页脚 HTML 代码