工作中积攒的几个自定义SQL函数:
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: 字符串切割函数
-- =============================================
ALTER function [dbo].[Split]
(
@Text nvarchar(4000),
@Sign nvarchar(4000)
)
returns @tempTable table(id int identity(1,1) primary key,[value] nvarchar(4000))
AS
begin
declare @StartIndex int --开始查找的位置
declare @FindIndex int --找到的位置
declare @Content varchar(4000) --找到的值
set @StartIndex = 1
set @FindIndex=0
--遍历每个字符
while(@StartIndex <= len(@Text))
begin
SELECT @FindIndex = charindex(@Sign,@Text,@StartIndex)
--判断有没找到 没找到返回0
IF(@FindIndex =0 OR @FindIndex IS NULL)
begin
set @FindIndex = len(@Text)+1
end
set @Content = ltrim(rtrim(substring(@Text,@StartIndex,@FindIndex-@StartIndex)))
set @StartIndex = @FindIndex+1
insert into @tempTable ([value]) values (@Content)
end
return
end
-- ==========
