前导或后导字符串

根据需求，需要把某一些数字或字符串进行格式化，前导或后导字符串。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