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
Auf dieser Seite können Sie das Online-Video T-SQL Tutorial - REPLICATE mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer BeardedDev 13 Juli 2020 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 776 Mal angesehen und es wurde von 28 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
