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
