Скачать или смотреть T-SQL Tutorial - REPLICATE

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

If you like the videos you can support me on Patreon,   / beardeddev  

Have you ever wondered how to leading 0s in SQL Server? What about creating a histogram? In this T-SQL Tutorial I show you how you can do both using the REPLICATE function. Adding leading 0s is something I have to do a lot in my work life and creating histograms gives you a good visual representation of the variances in the data.

You can use the code examples below to follow along.

Please feel free to post any comments

Code Examples:
IF OBJECT_ID(N'dbo.Invoice', N'U') IS NOT NULL
DROP TABLE dbo.Invoice;

GO

CREATE TABLE dbo.Invoice
(
InvoiceKey INT NOT NULL IDENTITY(1, 1),
InvoiceNumber CHAR(10) NOT NULL,
InvoiceAmount DECIMAL(10, 2) NOT NULL
)

DECLARE @i INT = 1

WHILE @i (add less than symbol here)= 10

BEGIN

INSERT INTO dbo.Invoice (InvoiceNumber, InvoiceAmount)
SELECT
POWER(@i, 3) AS InvoiceNumber,
CAST(RAND() * (10000 * SQUARE(@i)) AS DECIMAL(10, 2)) AS InvoiceAmount

SET @i = @i + 1

END

SELECT
InvoiceKey,
REPLICATE('0', 10 - LEN(InvoiceNumber)) + InvoiceNumber AS InvoiceNumber,
InvoiceAmount
FROM dbo.Invoice

UPDATE dbo.Invoice
SET InvoiceNumber = REPLICATE('0', 10 - LEN(InvoiceNumber)) + InvoiceNumber
WHERE LEN(InvoiceNumber) (add not equal too here) 10

SELECT
*,
CAST(100 * InvoiceAmount / SUM(InvoiceAmount) OVER(ORDER BY (SELECT NULL)) AS INT) AS PctofTotal,
REPLICATE('*', CAST(100 * InvoiceAmount / SUM(InvoiceAmount) OVER(ORDER BY (SELECT NULL)) AS INT)) AS [Histogram]
FROM dbo.Invoice