这个题目描述的有点乱,大家看需求吧,论坛帖子的需求, 实现类似程序中,可以用string[] arr=“1.12.123”.split(.) 如果我要取12 则可以arr[1]。
CREATE FUNCTION dbo.F_SplitByIdex
(
@SplitString nvarchar(max), --源字符串
@Separator nvarchar(10)=' ', --分隔符号,默认为空格
@index INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
DECLARE @SplitStringsTable TABLE --输出的数据表
(
[id] int identity(1,1),
[value] 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
SET @ReturnText = (SELECT TOP 1 value FROM @SplitStringsTable WHERE id= @index+1)
RETURN @ReturnText
END
GO
测试如下:
Declare @str varchar(10)='1.12.123'
SELECT dbo.F_SplitByIdex(@str,'.',0) --中间的是分隔符可以是逗号、句号、分号等
结果: