Fill in the number of parameters from the padleft and padright strings of SQL Server

Yesterday, my colleague asked me if SQL Server has any such information.. NET Framework. padleft, String. the padright method does not have the correct internal letter number, but there is a similar replicate string number, it is used to realize the character filling function in the SQL Server String.

The sampling method is not recommended. A new parameter number is added, which includes three parameters: original string-@ source, fill in character-@ paddingchar, return String Length-@ totalwidth, please refer to the following program example:

CREATE FUNCTION [dbo].[PadLeft]

(

@Source nvarchar(30),       -- Original string

@PaddingChar nchar(1),      -- Fill in the delimiter

@TotalWidth tinyint         -- Overall length

)

RETURNS nvarchar(285)

AS

BEGIN

DECLARE @Result nvarchar(285)

SELECT @Result = REPLICATE(@PaddingChar, @TotalWidth - LEN(@Source)) + @Source

RETURN @Result

END

GO

The replicate statement is based on the number of string functions created in SQL Server. The replicate statement is used to specify the number of unique characters (the total length-the length of the original string ), in addition, the original string is used to complete the left-side padding function. Similarly, if you enter "Replicate" in the "right" field, the original string is placed before the replicate function.

The usage is also very simple. After adding padleft and padright In the northwind data warehouse, check the following rows:

01	USE [Northwind]

02

GO

03

04	SELECT TOP 5 dbo.PadLeft([ProductID], '0', 8) AS [SeqNo], ProductID, ProductName, QuantityPerUnit, UnitPrice

05	FROM Products

06

GO

07

08	SELECT TOP 5 dbo.PadRight([ProductID], '0', 8) AS [SeqNo], ProductID, ProductName, QuantityPerUnit, UnitPrice

09	FROM Products

10

GO

For the effect, set the productid score to 0 on the left and right to 8 sequence values:

Reference to: http://www.dotblogs.com.tw/hunterpo/archive/2009/08/18/10142.aspx