前导或后导字符串

根据需求，需要把某一些数字或字符串进行格式化，前导或后导字符串。insus.net把这个功能写成一个自定义函数。需要时，直接使用即可。

 

set ansi_nulls on
go
set quoted_identifier on
go

-- =============================================
-- author:      insus.net
-- create date: 2019-05-10
-- update date: 2019-05-10
-- description: 前导或后导字符
-- =============================================
create function [dbo].[svf_leadingstring]
    (
        @originalcharacter nvarchar(max),
        @expectedlength int,
        @padcharacterstring nvarchar(max),
        @leadingtoright bit
    )
returns nvarchar(max)
as
begin
    declare @rtv nvarchar(max) = @originalcharacter

    if len(isnull(@originalcharacter,'')) < @expectedlength
    begin
        declare @replicatestring nvarchar(max) = replicate(@padcharacterstring, @expectedlength - len(@originalcharacter))
        
        if @leadingtoright = 1        
            set @rtv = @originalcharacter + @replicatestring        
        else        
            set @rtv = @replicatestring + @originalcharacter
    end
              
    return @rtv
end

go

 

举例可以更好说明函数使用如何。

 

--创建临时表，并随机添加一些数据
declare @dumptable as table ([originalcharacter] nvarchar(max))
insert into @dumptable  ([originalcharacter])  values (12)
insert into @dumptable  ([originalcharacter])  values (3456)
insert into @dumptable  ([originalcharacter])  values ('rt')
insert into @dumptable  ([originalcharacter])  values ('gfr')
insert into @dumptable  ([originalcharacter])  values ('345e')
insert into @dumptable  ([originalcharacter])  values (43)
insert into @dumptable  ([originalcharacter])  values (7777)
insert into @dumptable  ([originalcharacter])  values (254687)
insert into @dumptable  ([originalcharacter])  values ('adrf')


--设置自定义函数的参数
declare @expectedlength int = 8, @padcharacterstring nvarchar(max) = '0'


select [originalcharacter],
    [dbo].[svf_leadingstring]([originalcharacter],@expectedlength,@padcharacterstring,0) as [leadingtoleft],
    [dbo].[svf_leadingstring]([originalcharacter],@expectedlength,@padcharacterstring,1) as [leadingtoright] 
from @dumptable