SQL Server UDF to pad a string

http://www.mssqltips.com/sqlservertip/1738/sql-server-udf-to-pad-a-string/

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

declare @l varchar(50) set @l='3 ' select @l=ltrim(rtrim('3')) select len(@l)   SELECT RIGHT('000'+ISNULL(@l,''),3)   select left('00'+rtrim('3'),2) -- select right('000'+ltrim(3),3) -- SELECT RIGHT('000'+CAST(3 AS VARCHAR(3)),3)   --The function expects four parameters: /* 附：oracle补零   1.前端补0： Sql代码  select lpad('345',8,'0') from dual;   select to_char('345','00000000') from dual;    select lpad('345',8,'0') from dual;  select to_char('345','00000000') from dual;  2.后端补0： Sql代码  select rpad('345',8,'0') from dual;  select rpad('345',8,'0') from dual; C#代码实现方法： int a = 1; string str = a.ToString("000"); //或 string astr = a.ToString().PadLeft(3,'0');   http://www.mssqltips.com/sqlservertip/1738/sql-server-udf-to-pad-a-string/ @string_unpadded - the raw string value you wish to pad. @pad_char - the single character to pad the raw string. @pad_count - the amount of times to repeat the padding character @pad_pattern -  an integer value that determines where to insert the pad character 0 - all pad characters placed left of the raw string value (pad left) 1 - all pad characters placed right of the raw string value (pad right) 2 - all pad characters placed at the midpoint of the raw string value (pad center) 3 - the raw string value will be centered between the pad characters (pad ends) */ if object_id('[dbo].[usp_pad_string]') is not null drop function [dbo].[usp_pad_string] go CREATE FUNCTION [dbo].[usp_pad_string]   (  @string_unpadded VARCHAR(100),   @pad_char VARCHAR(1),   @pad_count tinyint,   @pad_pattern INT) RETURNS VARCHAR(100) AS BEGIN  DECLARE @string_padded VARCHAR(100)    SELECT @string_padded =   CASE @pad_pattern    WHEN 0 THEN REPLICATE(@pad_char, @pad_count) + @string_unpadded --pad left    WHEN 1 THEN @string_unpadded + REPLICATE(@pad_char, @pad_count) --pad right    WHEN 2 THEN       --pad center      LEFT(@string_unpadded, FLOOR(LEN(@string_unpadded)/2))       + REPLICATE(@pad_char, @pad_count)       + RIGHT(@string_unpadded, LEN(@string_unpadded) - FLOOR(LEN(@string_unpadded)/2))     WHEN 3 THEN       --pad edges      REPLICATE(@pad_char, FLOOR(@pad_count/2))       + @string_unpadded       + REPLICATE(@pad_char, @pad_count - FLOOR(@pad_count/2))    END  RETURN @string_padded END GO --測試 --Even distribution possible --Pad Left SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 0) AS [padded string], '0 - pad LEFT' AS [pad pattern value];   --Pad Right SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 1) AS [padded string], '1 - pad RIGHT' AS [pad pattern value];   --Pad Center SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 2) AS [padded string], '2 - pad CENTER' AS [pad pattern value];   --Pad Edges SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 3) AS [padded string], '3 - pad EDGES' AS [pad pattern value];     --在右边加几位 declare @l varchar(20),@len int,@count int set @l='1' select @len=len(@l) if(@len<6) select @count=6-@len select [dbo].[usp_pad_string] (@l,'0',@count,1)   -- select [dbo].[usp_pad_string] ('1','0',6-len('1'),1)