谨以此文记住自己写的函数:
背景条件:SQL Server 2008
想要实现的效果 1001==》》1200000001001
函数内容:
自定义函数,用指定的开始字符串填充字段的左侧
函数名:LFORMAT
参数:@SourceStr;原始字段varchar(50)
@NewFirstWord :填充后的首字符,最多5位 varchar型
@NewIdLength:填充后的长度,int型
@ExceptWord:要排除的值
create function LFORMAT(@SourceStr varchar(50),@NewFirstWord varchar(5),@NewIdLength int,@ExceptWord varchar(5)='')
returns varchar(50)
as
begin
declare @l varchar(50)
declare @s varchar(50)
set @l=''
set @s=''
if(@SourceStr is null)
begin
select @l=@SourceStr
end
if(@ExceptWord <> '')
begin
if((select CHARINDEX(@ExceptWord,@SourceStr,0))>0)
begin
select @l=@SourceStr
end
else
begin
select @l = replicate('0',@NewIdLength-LEN(@SourceStr))+@SourceStr
if(LEN(@l)>@NewIdLength)
begin
select @s = Right(@l,@NewIdLength-LEN(@NewFirstWord))
select @l = @NewFirstWord+@s
end
else
begin
select @l = replicate('0',@NewIdLength-LEN(@SourceStr))+@SourceStr
select @s = Right(@l,@NewIdLength-LEN(@NewFirstWord))
select @l = @NewFirstWord+@s
end
end
end
else
begin
select @l = replicate('0',@NewIdLength-LEN(@SourceStr))+@SourceStr
if(LEN(@l)>@NewIdLength)
begin
select @s = Right(@l,@NewIdLength-LEN(@NewFirstWord))
select @l = @NewFirstWord+@s
end
else
begin
select @l = replicate('0',@NewIdLength-LEN(@SourceStr))+@SourceStr
select @s = Right(@l,@NewIdLength-LEN(@NewFirstWord))
select @l = @NewFirstWord+@s
end
end
return @l
end
例子: LFORMAT('2001','12',10,default) 返回值:12000002001
或 LFORMAT(列名,'1',10,default)
LFORMAT('CHINA','12',10,'CHINA') 返回值:CHINA
或 FORMAT(列名,'12',10,'CHINA') 那么,这个列中,所有包含CHINA的值不会被替换,其他值则替换