【转】sqlserver中的Lpad 和 Rpad

最新推荐文章于 2022-04-24 09:16:55 发布

原创最新推荐文章于 2022-04-24 09:16:55 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#Go #SQL

本文介绍了一个SQL Server中自定义的T-SQL函数，用于在字符串左侧或右侧填充指定字符以达到固定长度。提供了左侧填充（LPad）和右侧填充（RPad）两个函数的实现代码及使用示例。

From :

http://www.developerdotstar.com/community/node/319

Code :

/**************** LPad ***********************/

IF EXISTS (

SELECT *

FROM dbo.sysobjects

WHERE id = object_id(N'[dbo].[LPad]')

AND xtype IN (N'FN', N'IF', N'TF')

)

DROP FUNCTION [dbo].[LPad]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE FUNCTION LPad( @PadChar char(1), @PadToLen int, @BaseString varchar(100))

RETURNS varchar(1000)

AS

/* ****************************************************

Author: Daniel Read

Description: Pads @BaseString to an exact length (@PadToLen)

using the specified character (@PadChar). Base string will not be

trimmed. Implicit type conversion should allow caller to

pass a numeric T-SQL value for @BaseString.

Unfortunately T-SQL string variables must be declared with an

explicit width, so I chose 100 for the base and 1000 for the

return. Feel free to adjust data types to suit your needs.

Keep in mind that if you don't assign an explicit width to

varchar it is the same as declaring varchar(1).

Revision History: Date

Name Description ---- ---- -----------

***************************************************** */

BEGIN

DECLARE @Padded varchar(1000)

DECLARE @BaseLen int

SET @BaseLen = LEN(@BaseString)

IF @BaseLen >= @PadToLen

BEGIN

SET @Padded = @BaseString

END ELSE BEGIN

SET @Padded = REPLICATE(@PadChar, @PadToLen - @BaseLen) + @BaseString

END

RETURN @Padded

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

例子：

SELECT dbo.LPad('0', 3, '1')

001

/**************** RPad ***********************/

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[RPad]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[RPad]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION RPad(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100))
RETURNS varchar(1000)
AS
/* ****************************************************
Author: Daniel Read
Description: Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History: Date
Name Description ---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END ELSE BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

例子：

select dbo.Rpad('0',3,'1')

100