【永利澳门游戏网址304】SQL Server UDF to pad a string

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 character0 - 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 ENDGO--測試--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 intset @l='1'select @len=len(@l)if(@len<6)select @count=6-@lenselect [dbo].[usp_pad_string] (@l,'0',@count,1)--select [dbo].[usp_pad_string] ('1','0',6-len('1'),1)

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注