【SQL Server】Sql实现的字符串分割自定义函数:Split
分类:数据库| 发布:camnprbubuol| 查看: | 发表时间:2010/9/30
- Create FUNCTION [dbo].[SplitToTable]
- (
- @SplitString nvarchar(max),
- @Separator nvarchar(10)=’ ’
- )
- RETURNS @SplitStringsTable TABLE
- (
- [id] int identity(1,1),
- [value] nvarchar(max)
- )
- AS
- BEGIN
- DECLARE @CurrentIndex int;
- DECLARE @NextIndex int;
- DECLARE @ReturnText nvarchar(max);
- SELECT @CurrentIndex=1;
- WHILE(@CurrentIndex<=len(@SplitString))
- BEGIN
- SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
- IF(@NextIndex=0 OR @NextIndex IS NULL)
- SELECT @NextIndex=len(@SplitString) 1;
- SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
- INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
- SELECT @CurrentIndex=@NextIndex 1;
- END
- RETURN;
- END
select * FROm dbo.SplitToTable(’111,b2222,323232,32d,e,323232f,g3222’, ’,’)
结果为
id value
----------- ---------------------------------------
1 111
2 b2222
3 323232
4 32d
5 e
6 323232f
7 g3222
(7 行受影响)