前導或後導字元串

根據需求，需要把某一些數字或字元串進行格式化，前導或後導字元串。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