SP过程中调用的SQL方法 将复数品号拼接为条件
USE [local]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 2017/4/12 8:45:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(@SourceText varchar(max), @Delimiter char(1))
returns @Table table(StringID int, Value varchar(max))
AS
begin
declare @count integer
declare @curPos integer
declare @textPartLength integer
declare @prevPos integer
declare @nextOcc integer
declare @textPart varchar(max)
set @count = 1
set @prevPos = 0
set @curPos = 0
set @nextOcc = charindex(@Delimiter, @SourceText)
while @nextOcc > 0
begin
set @curPos = @nextOcc
set @nextOcc = charindex(@Delimiter, @SourceText, @nextOcc + 1)
set @textPartLength = @curPos - @prevPos
-- grab the substring from the source text and remove the delimitera
set @textPart = substring(@SourceText, @prevPos , @textPartLength)
set @textPart = replace(@textPart, @Delimiter, '')
insert into @Table (StringID, Value) values(@count, @textPart)
set @prevPos = @curPos
set @count += 1
end
-- get any text after the last delimiter
set @textPartLength = LEN(@SourceText) - (@prevPos - 1)
set @textPart = substring(@SourceText, @prevPos , @textPartLength)
set @textPart = replace(@textPart, @Delimiter, '')
if(LEN(@textPart) > 0)
insert into @Table values (@count, @textPart)
return
end
