Create function [dbo].[split] ( @SourceSql varchar(max), @StrSeprate varchar(10) ) returns @temp table(line varchar(max)) as begin declare @i int set @SourceSql = rtrim(ltrim(@SourceSql)) set @i = charindex(@StrSeprate,@SourceSql) while @i >= 1 begin if len(left(@SourceSql,@i-1))>0 begin insert @temp values(left(@SourceSql,@i-1)) end set @SourceSql=substring(@SourceSql,@i+len(@StrSeprate),len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql <> '' insert @temp values(@SourceSql) return end ---------------------------------------------------------------------------------------------------------------------------------- --@column 表示字段或者常量,@paddingChar 表示 补位字符, @len 补位数量, @returnStr create function [dbo].[PadLeft](@column varchar(16),@paddingChar char(1),@len int) returns varchar(16) as begin declare @returnStr varchar(16) select @returnStr = isnull(replicate(@paddingChar,@len - len(isnull(@column ,0))), '') + @column return @returnStr end
调用:Select * From dbo.split('1003,1004,1005',',')
结果如下:
100310041005
select dbo.PadLeft(2,0,5)