SQL里面用自定义Split()完成个性化需求
时间:2024-01-15 08:07:58
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[SplitString]
(
@Input nvarchar(max),
@Separator nvarchar(max)=',',
@RemoveEmptyEntries bit=1
)
returns @TABLE table
(
[Id] int identity(1,1),
[Value] nvarchar(max)
)
as
begin
declare @Index int, @Entry nvarchar(max)
set @Index = charindex(@Separator,@Input)
while (@Index>0)
begin
set @Entry=ltrim(rtrim(substring(@Input, 1, @Index-1)))
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
begin
insert into @TABLE([Value]) Values(@Entry)
end
set @Input = substring(@Input, @Index+datalength(@Separator)/2, len(@Input))
set @Index = charindex(@Separator, @Input)
end
set @Entry=ltrim(rtrim(@Input))
if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
begin
insert into @TABLE([Value]) Values(@Entry)
end
return
end
函数、表都建好了,下面调用测试一下吧:
declare @str1 varchar(max), @str2 varchar(max), @str3 varchar(max)
set @str1 = '1,2,3'
set @str2 = '1###2###3'
set @str3 = '1###2###3###'
select [Value] from [dbo].[SplitString](@str1, ',', 1)
select [Value] from [dbo].[SplitString](@str2, '###', 1)
select [Value] from [dbo].[SplitString](@str3, '###', 0)
结果,截个图来看一下:
标签:Split,自定义函数
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python列表生成器的循环技巧分享
2023-10-06 21:24:28
![](https://img.aspxhome.com/file/2023/8/63238_0s.png)
用XsltArgumentList实现xsl的参数调用
2008-09-04 11:24:00
Python中使用dom模块生成XML文件示例
2023-08-02 20:07:31
TensorFlow 模型载入方法汇总(小结)
2022-11-09 00:05:42
![](https://img.aspxhome.com/file/2023/2/86462_0s.png)
python绘制浅色范围曲线的示例代码
2021-07-03 02:34:10
![](https://img.aspxhome.com/file/2023/0/127020_0s.png)
JS代码格式化和语法着色
2013-07-14 19:47:45
python将unicode和str互相转化的实现
2023-07-17 21:36:34
ORACLE常见错误代码的分析与解决(二)
2010-08-02 13:31:00
SQL Server中的SQL语句优化与效率问题
2024-01-20 05:26:57
Python常用工具之音频调整音量
2023-10-29 03:15:33
![](https://img.aspxhome.com/file/2023/6/102816_0s.jpg)
python如何代码集体右移
2023-07-08 01:34:28
mysql 8.0 错误The server requested authentication method unknown to the client解决方法
2024-01-13 01:41:25
Pycharm远程连接服务器并运行与调试
2021-05-29 04:38:51
![](https://img.aspxhome.com/file/2023/0/130960_0s.jpg)
python删除列表中特定元素的几种方法
2023-12-21 02:17:12
![](https://img.aspxhome.com/file/2023/5/85315_0s.png)
用python写个博客迁移工具
2023-06-09 05:13:14
![](https://img.aspxhome.com/file/2023/2/69002_0s.gif)
Python开发常用五种循环方式的场景性能比较
2021-11-19 13:24:00
JavaScript生成二维码图片小结
2024-05-02 16:13:16
![](https://img.aspxhome.com/file/2023/8/132868_0s.png)
windows环境下mysql的解压安装及备份和还原
2024-01-27 05:55:48
![](https://img.aspxhome.com/file/2023/5/75635_0s.png)
python client使用http post 到server端的代码
2021-09-03 14:33:54
Python 不同对象比较大小示例探讨
2023-06-11 01:13:32