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