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.

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

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

[agrebibennbeynadia]

This is the best tutorial, now I can get zip code as 5 digits. Thank you

[pabeader]

That's a handy way to make a bar chart! Could you also do a histogram?

[superfreiheit]

Good video. Can you also make a video about =Hyperlink and how to create it with two or more columns together.

[aleksanderstrommer]

What if I want to plot numbers like > 100, can I make histogram smaller?

[superfreiheit]

Is there a reason to use sum as windows function instead of a subquery?

SELECT *, InvoiceAmount / ( SELECT sum(InvoiceAmount) FROM dbo.Invoice )
, InvoiceAmount / ( sum(InvoiceAmount) OVER (Order by (Select NULL)) )
FROM dbo.Invoice


same results