SQL Server UDF to pad a string-CSDN博客

http://www.mssqltips.com/sqlservertip/1738/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 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)
转载于:https://www.cnblogs.com/geovindu/p/3731955.html