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
On this page of the site you can watch the video online T-SQL Tutorial - REPLICATE with a duration of hours minute second in good quality, which was uploaded by the user BeardedDev 13 July 2020, share the link with friends and acquaintances, this video has already been watched 776 times on youtube and it was liked by 28 viewers. Enjoy your viewing!
