SQLServerCentral » SQL Server 2008 » T-SQL (SS2K8) » concatenate with leading zerosInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralWed, 18 Jan 2017 12:26:00 GMT20concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492913.aspxHi Everyone
I am creating a view which involved concatenation of 2 int columns.
The data in the columns look like
Column 1 Column 2
1234 1
12345 11
I am trying to get the following output
001234001
012345011
So the first column should have zeros padded to the front to make 6 numbers, the second column should be 3 numbers long with zeros in front. So when added together it is 9 numbers long.
Thanks in advance.
Wed, 01 Oct 2014 08:01:59 GMTJohnny HRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1621566.aspxAnyone wish to check how 2012 version would do?
[code="sql"]
select FORMAT(C1*1000+c2,'000000000')
from #TestTable
[/code]
;-)
Wed, 01 Oct 2014 08:01:59 GMTEugene ElutinRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1621479.aspxMore Integer Math less RIGHT :)
[code="sql"]PRINT '========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = right(1000000000 + 1000*c1 + c2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;[/code]
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 968 ms, elapsed time = 933 ms.
========== Integer Math RIGHT Method ==========
SQL Server Execution Times:
CPU time = 782 ms, elapsed time = 804 ms.
P.S. Oh, a 1+ year old topic. Problems with my DATE math. :(Wed, 01 Oct 2014 03:00:13 GMTserg-52RE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1621380.aspxTry this
SELECT RIGHT('000000' + CAST(Col1 as VARCHAR), 6) + RIGHT('000' + CAST(Col2 as VARCHAR), 3)
Hope this helps.
Edit:
Just saw this was an older post lolTue, 30 Sep 2014 15:12:32 GMTandrewboldingRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493722.aspx[quote]
Apologies for not being clear... I've been in a real crunch for the last week or so.
What I meant was just exactly what you said. You write the data once and then test against it and the WHERE clauses prevent any additional writes. The problem is that still brings the I/O system, whatever it is for a given machine, into play and you start measuring IO burps instead of just the algorithm. That's why I do the dump to the variable, instead. As I said there, the variable is reassigned a new value for every row processed. To be sure, though, if we were testing the effectivity (to coin a phrase) of WHERE clauses, your test harness would be a good one for that.
[/quote]
Man! I have spent way more time on this than I had planned, but I spend a lot of my time wringing speed out of my stored procedures and I find this issue to be fascinating.
I made 2 versions of the test harness: 1 that uses the @BitBucket method and 1 that uses the WHERE clause method (full text of both below). I ran both tests on a physical machine to try to reduce the variability in the timings. Somewhat surprisingly, the WHERE clause method is faster than the @BitBucket method by about 15%.
Getting back to the original question, it appears that using numbers or variables in the formula does not make any measurably consistent difference, but using the SUBSTRING construct does appear to be slightly faster than using RIGHT.
Here are the results:
[code="other"]
@BitBucket Method
First Run Second Run
-------------------- --------------------
RIGHT SUBSTRING RIGHT SUBSTRING
-------------------- --------------------
All Numbers 5538 5772 5304 5226
Billion 6116 5445 5288 5164
Both 6099 5195 5507 5179
Total time for all 12 runs: 65833
[/code]
[code="other"]
WHERE Clause Method
First Run Second Run
-------------------- --------------------
RIGHT SUBSTRING RIGHT SUBSTRING
-------------------- --------------------
All Numbers 4587 4399 4976 4477
Billion 4633 4368 4961 4696
Both 4711 4462 4742 5132
Total time for all 12 runs: 56144
[/code]
@BitBucket method test script:
[code="sql"]
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT
);
INSERT INTO [#TestTable] ( [C1], [C2] )
SELECT TOP 10000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @BitBucket CHAR(9);
DECLARE @Billion INT;
SET @Billion = 1000000000;
DECLARE @Thousand INT;
SET @Thousand = 1000;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
WHERE clause test script:
[code="sql"]
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT,
[Result] CHAR(9),
);
INSERT INTO [#TestTable] ( [C1], [C2], [Result] )
SELECT TOP 10000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
,'000000000'
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
-- Initialize the Result column value
UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9);
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Billion INT;
SET @Billion = 1000000000;
DECLARE @Thousand INT;
SET @Thousand = 1000;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
[/code]Wed, 11 Sep 2013 08:34:20 GMTpaul.s.lachRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493683.aspx[quote][b]paul.s.lach (9/11/2013)[/b][hr]
Jeff, I do not believe my test harness is writing to disk after the initial setup of the temporary table. By using the WHERE clause in each of the timed sections I believe none of the rows will test as true since the [Result] column already contains the correct 9 character value. However, you know how SQL Server works a heck of a lot better than I do so I am wondering, is my assumption that I have eliminated writing to the disk correct?
[/quote]
Apologies for not being clear... I've been in a real crunch for the last week or so.
What I meant was just exactly what you said. You write the data once and then test against it and the WHERE clauses prevent any additional writes. The problem is that still brings the I/O system, whatever it is for a given machine, into play and you start measuring IO burps instead of just the algorithm. That's why I do the dump to the variable, instead. As I said there, the variable is reassigned a new value for every row processed. To be sure, though, if we were testing the effectivity (to coin a phrase) of WHERE clauses, your test harness would be a good one for that.
[quote]
Also, I am running on a virtual machine with 8 GB assigned and the virtual machine thinks it has 2 quad-core processors. When I am doing the test runs my connection is the only active connection. Wait a minute, I think I may have just answered the question about the variability. I just ran the test harness on a physical machine and got much more consistent results so I think the variability issue is caused by the fact that SQL Server is running on a virtual machine.
[/quote]
I'll have to do that when I get home.Wed, 11 Sep 2013 07:38:40 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493655.aspx[quote]Part of the reason why Paul S. might be seeing some instability is because he didn't trust the @BitBucket variable and took to writing to disk. Depending on what else is running, there could be substantial disruptions that you wouldn't see on a quieter machine.[/quote]
Jeff, I do not believe my test harness is writing to disk after the initial setup of the temporary table. By using the WHERE clause in each of the timed sections I believe none of the rows will test as true since the [Result] column already contains the correct 9 character value. However, you know how SQL Server works a heck of a lot better than I do so I am wondering, is my assumption that I have eliminated writing to the disk correct?
Also, I am running on a virtual machine with 8 GB assigned and the virtual machine thinks it has 2 quad-core processors. When I am doing the test runs my connection is the only active connection. Wait a minute, I think I may have just answered the question about the variability. I just ran the test harness on a physical machine and got much more consistent results so I think the variability issue is caused by the fact that SQL Server is running on a virtual machine.
If you run the test on your machine, what do your results look like (please post)?
Here is the full test harness again:
[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT,
[Result] CHAR(9),
);
INSERT INTO [#TestTable] ( [C1], [C2], [Result] )
SELECT TOP 10000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
,'000000000'
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
-- Initialize the Result column value
UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9);
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Billion INT;
SET @Billion = 1000000000;
DECLARE @Thousand INT;
SET @Thousand = 1000;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
[/code]Wed, 11 Sep 2013 07:06:29 GMTpaul.s.lachRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493615.aspx[quote][b]dwain.c (9/10/2013)[/b][hr]Paul - I get fairly stable results with this test harness. Can you confirm?
I am also running on a SQL Server that is fairly quiescent (no other processes running).
[/quote]
Even with the recompile option I am getting similarly variable results. I am running on a virtual machine where, at the time of the testing, I am the only active connection. I have 8 GB of memory assigned and the virtual machine thinks it has 2 quad-core processors.
Can you post your results so I can see your timings?Wed, 11 Sep 2013 06:23:16 GMTpaul.s.lachRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493430.aspxPart of the reason why Paul S. might be seeing some instability is because he didn't trust the @BitBucket variable and took to writing to disk. Depending on what else is running, there could be substantial disruptions that you wouldn't see on a quieter machine.Tue, 10 Sep 2013 21:19:20 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493418.aspxPaul - I get fairly stable results with this test harness. Can you confirm?
I am also running on a SQL Server that is fairly quiescent (no other processes running).
[code="sql"]
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT,
[Result] CHAR(9),
);
INSERT INTO [#TestTable] ( [C1], [C2], [Result] )
SELECT TOP 10000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
,'000000000'
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
-- Initialize the Result column value
UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
OPTION (RECOMPILE);
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Billion INT;
SET @Billion = 1000000000;
DECLARE @Thousand INT;
SET @Thousand = 1000;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
[/code]Tue, 10 Sep 2013 19:36:07 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493400.aspx[quote][b]Jeff Moden (9/10/2013)[/b][hr][quote][b]dwain.c (9/10/2013)[/b][hr](you forgot my version in your test harness).
[/code][/quote]
Who you talking to, Dwain?[/quote]
Paul.S here: [url]http://www.sqlservercentral.com/Forums/FindPost1493158.aspx[/url]Tue, 10 Sep 2013 17:53:20 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493353.aspx[quote][b]paul.s.lach (9/10/2013)[/b][hr]One of the things I thought of was an article I read that said that the compiler optimizes "SELECT @Variable = ..." to just set it to the first value when you select from a table with more than one row (I would reference the article, but I do not remember it and do not want to spend any more time on this than I already have!). [/quote]
Not quite right. It will be given the value of each row and whichever row is the last row processed by the query is the value the variable will have. It's an important trait for doing things such as the "Quirky Update", which can be used for doing very high speed running totals and the like.Tue, 10 Sep 2013 13:55:17 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493352.aspx[quote][b]dwain.c (9/10/2013)[/b][hr](you forgot my version in your test harness).
[/code][/quote]
Who you talking to, Dwain?Tue, 10 Sep 2013 13:51:52 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493283.aspxOK, this one really sucked me in for more than I planned on doing!
I was surprised by the variability in the results and thought that there had to be something other than the algorithms we were testing affecting the results. One of the things I thought of was an article I read that said that the compiler optimizes "SELECT @Variable = ..." to just set it to the first value when you select from a table with more than one row (I would reference the article, but I do not remember it and do not want to spend any more time on this than I already have!). I also thought that the short durations might be affecting the results.
Therefore, I modified the code to do the following:
1. add a column to the temporary table to hold the result of the calculation
2. increase the number of records from 1,000,000 to 10,000,000
3. modify the queries to UPDATE where the calculated value is not equal to the current value
The key thing here is that the queries should never modify the table because the calculated value will always be equal to the stored value, but the values will have to be calculated for every row. This should ensure that the timings are representing the time it takes to do the calculations and the comparisons and (hopefully) nothing else.
I also modified the test to do 6 different calculations:
1. All numbers using RIGHT
2. All numbers using SUBSTRING
3. Billion variable and 1000 using RIGHT
4. Billion variable and 1000 using SUBSTRING
5. Billion variable and Thousand variable using RIGHT
6. Billion variable and Thousand variable using SUBSTRING
This seemed like a good plan, but the results continue to be variable:
[code="other"]
First Run Second Run
-------------------- --------------------
RIGHT SUBSTRING RIGHT SUBSTRING
-------------------- --------------------
All Numbers 5704 5047 5078 6047
Billion 6469 4953 5843 5906
Both 5953 4891 6281 5219
[/code]
Here is the script I used for the test:
[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT,
[Result] CHAR(9),
);
INSERT INTO [#TestTable] ( [C1], [C2], [Result] )
SELECT TOP 10000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
,'000000000'
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
-- Initialize the Result column value
UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9);
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Billion INT;
SET @Billion = 1000000000;
DECLARE @Thousand INT;
SET @Thousand = 1000;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
[/code]
If anyone can explain the variability in the timings I would be interested in hearing about it.Tue, 10 Sep 2013 10:22:14 GMTpaul.s.lachRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493240.aspxYou guys are awesome. That's why I love this site, it reminds me to think different to look for better results. :-)Tue, 10 Sep 2013 09:05:40 GMTLuis CazaresRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493169.aspxTry running this 3-4 times and you'll see that the winner bounces around among the 3 (you forgot my version in your test harness).
[code="sql"]
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT
);
INSERT INTO [#TestTable] ( [C1], [C2] )
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @BitBucket CHAR(9);
PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Alternate Math + Right Method =========='
DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Second Alternate Math + Right Method =========='
DECLARE @Thousand INT;
SET @Thousand = 1000;
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Here are sample results from one run on my machine:
[code="plain"]
========== Another Integer Math Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 388 ms.
========== Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 400 ms.
========== Second Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 405 ms, elapsed time = 395 ms.
[/code]Tue, 10 Sep 2013 07:21:50 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493158.aspxInterestingly, by using a variable instead of 1000000 it was faster, but using a variable instead of 1000 makes it slower!
Here is the script I used for testing followed by my results (sorry my machine is not as fast as Jeff's!!!)
[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT
);
INSERT INTO [#TestTable] ( [C1], [C2] )
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @BitBucket CHAR(9);
PRINT '
========== Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Double STUFF Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Alternate Math + Right Method =========='
DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Second Alternate Math + Right Method =========='
DECLARE @Thousand INT;
SET @Thousand = 1000;
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
[code="other"]
(1000000 row(s) affected)
========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 3093 ms, elapsed time = 3341 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 3547 ms, elapsed time = 3559 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 2391 ms, elapsed time = 2395 ms.
========== Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 2062 ms, elapsed time = 2070 ms.
========== Second Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 2235 ms, elapsed time = 2250 ms.
[/code]Tue, 10 Sep 2013 07:10:20 GMTpaul.s.lachRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493147.aspx[quote][b]paul.s.lach (9/10/2013)[/b][hr]Here is another variation on the math method that my testing says is a little faster:
DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/quote]
How does it perform if you set the constant 1000 to be a variable too?Tue, 10 Sep 2013 06:53:47 GMTChrisM@WorkRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493142.aspxHere is another variation on the math method that my testing says is a little faster:
DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
Tue, 10 Sep 2013 06:43:42 GMTpaul.s.lachRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1493019.aspx[quote][b]dwain.c (9/9/2013)[/b][hr][quote][b]Jeff Moden (9/9/2013)[/b][hr][code="sql"] PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Oh, now that's just bloody clever. You stayed away from anything character based until "display time". Well done![/quote]
Couldn't have done it without you actually. Your first post gave me the inspiration.
[face-to-palm] Why didn't I think of it initially![/quote]
Nice one, Dwain. Tight little examples like this make concepts easier to remember.Tue, 10 Sep 2013 01:26:11 GMTChrisM@WorkRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492993.aspx[quote][b]Jeff Moden (9/9/2013)[/b][hr][code="sql"] PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Oh, now that's just bloody clever. You stayed away from anything character based until "display time". Well done![/quote]
Couldn't have done it without you actually. Your first post gave me the inspiration.
[face-to-palm] Why didn't I think of it initially!Mon, 09 Sep 2013 23:43:17 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492992.aspx[quote][b]dwain.c (9/9/2013)[/b][hr]
[code="sql"] PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
[/quote]
Oh, now that's just bloody clever. You stayed away from anything character based until "display time". Well done!Mon, 09 Sep 2013 23:40:29 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492987.aspxAs you said, in the interests of science, I removed the duplicate scenario in your test harness, added back my original at the end and included an extra one based on that good old binary collation within REPLACE.
Test harness:
[code="sql"]
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @BitBucket CHAR(9);
PRINT '========== Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Less Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(REPLICATE('0',6)+CAST(C1 AS VARCHAR(6)),6) + RIGHT(REPLICATE('0',3)+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Double STUFF Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(CAST) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+CAST(C1*1000+C2 AS VARCHAR(9)),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== REPLACE(STR) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = REPLACE(STR(C1*1000+C2,9),' ','0')
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== REPLACE(STR) Method with BIN collation =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = REPLACE(STR(C1*1000+C2,9) COLLATE Latin1_General_BIN,' ','0')
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Test results:
[code="plain"]
========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 614 ms.
========== Less Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 593 ms, elapsed time = 608 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 889 ms, elapsed time = 890 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 432 ms.
========== Integer Math RIGHT(CAST) Method ==========
SQL Server Execution Times:
CPU time = 405 ms, elapsed time = 400 ms.
========== REPLACE(STR) Method ==========
SQL Server Execution Times:
CPU time = 2637 ms, elapsed time = 2660 ms.
========== REPLACE(STR) Method with BIN collation ==========
SQL Server Execution Times:
CPU time = 1451 ms, elapsed time = 1441 ms.
========== Another Integer Math Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 387 ms.
[/code]
Despite what some pundits may disclaim, competition is a really good thing to help improve people's code and coding style.Mon, 09 Sep 2013 23:29:28 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492985.aspx[quote][b]Jeff Moden (9/9/2013)[/b][hr]The results are, indeed, pretty close especially considering the number of rows. However, just imagine if you could make all of your code run twice as fast. "Mind the pennies and the dollars take care of themselves." :-P[/quote]
I'm always imagining that, but the imagineering is often easier than the engineering.Mon, 09 Sep 2013 23:12:18 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492984.aspxThe results are, indeed, pretty close especially considering the number of rows. However, just imagine if you could make all of your code run twice as fast. "Mind the pennies and the dollars take care of themselves." :-PMon, 09 Sep 2013 23:08:20 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492983.aspxMine seems to have gotten lost in the shuffle there in your last test harness, but I can see what you've done. Results look pretty close.
For some reason, I'm always forgetting those integer math approaches, or at least they're not the first thing that snaps into my brain.Mon, 09 Sep 2013 23:01:53 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492982.aspxIn the interest of science...
[code="sql"]
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @BitBucket CHAR(9);
PRINT '========== Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Less Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(REPLICATE('0',6)+CAST(C1 AS VARCHAR(6)),6) + RIGHT(REPLICATE('0',3)+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Double STUFF Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(CAST) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+CAST(C1*1000+C2 AS VARCHAR(9)),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== REPLACE(STR) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = REPLACE(STR(C1*1000+C2,9),' ','0')
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Results:
[code="plain"]========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
========== Less Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 546 ms, elapsed time = 547 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 718 ms, elapsed time = 718 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 456 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 457 ms.
[color="RED"][b]========== Integer Math RIGHT(CAST) Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 389 ms.[/b][/color]
========== REPLACE(STR) Method ==========
SQL Server Execution Times:
CPU time = 1482 ms, elapsed time = 1472 ms.
[/code]Mon, 09 Sep 2013 22:51:47 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492980.aspx[quote][b]Jeff Moden (9/9/2013)[/b][hr][quote][b]dwain.c (9/9/2013)[/b][hr]At the risk of being called a contrarian, this can be done without using RIGHT:
[code="sql"]
WITH SampleData (C1, C2) AS (
SELECT 1234, 1
UNION ALL SELECT 12345, 11
UNION ALL SELECT 123456, 111)
SELECT C1, C2
,STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM SampleData;
[/code][/quote]
Perhaps RIGHT is the right way to go though it does take a lot of rows to make a difference.
[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @BitBucket CHAR(9);
PRINT '========== Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Double STUFF Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Results:
[code="plain"]
(1000000 row(s) affected)
========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 496 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 702 ms, elapsed time = 697 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 458 ms.
[/code][/quote]
Indeed, perhaps your right but two RIGHTs don't make a wrong!
[code="sql"]
PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Latest results:
[code="plain"]
========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 593 ms, elapsed time = 593 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 936 ms, elapsed time = 938 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 438 ms.
========== Another Integer Math Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 390 ms.
[/code]Mon, 09 Sep 2013 22:38:45 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492979.aspx[quote][b]dwain.c (9/9/2013)[/b][hr]At the risk of being called a contrarian, this can be done without using RIGHT:
[code="sql"]
WITH SampleData (C1, C2) AS (
SELECT 1234, 1
UNION ALL SELECT 12345, 11
UNION ALL SELECT 123456, 111)
SELECT C1, C2
,STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM SampleData;
[/code][/quote]
Perhaps RIGHT is the right way to go though it does take a lot of rows to make a difference.
[code="sql"]--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @BitBucket CHAR(9);
PRINT '========== Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Double STUFF Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @BitBucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
[/code]
Results:
[code="plain"]
(1000000 row(s) affected)
========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 499 ms, elapsed time = 496 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 702 ms, elapsed time = 697 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 452 ms, elapsed time = 458 ms.
[/code]Mon, 09 Sep 2013 22:20:38 GMTJeff ModenRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492961.aspxAt the risk of being called a contrarian, this can be done without using RIGHT:
[code="sql"]
WITH SampleData (C1, C2) AS (
SELECT 1234, 1
UNION ALL SELECT 12345, 11
UNION ALL SELECT 123456, 111)
SELECT C1, C2
,STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM SampleData;
[/code]Mon, 09 Sep 2013 18:41:21 GMTdwain.cRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492929.aspxI'm not sure that's giving you the correct result. You said you need to have 6 characters on the first part and 3 on the second one. You need to use 2 RIGHT functions to accomplish that.Mon, 09 Sep 2013 15:30:20 GMTLuis CazaresRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492926.aspxselect right(replicate('0', 5) + cast(Col1 as varchar(6))+ replicate('0', 2)+ cast(Col1 as varchar(9)), 9)
gives me the correct values.Mon, 09 Sep 2013 14:58:46 GMTJohnny HRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492925.aspx[quote][b]John.Hagen (9/9/2013)[/b][hr]it turns out it is not working correctly. It is adding all the zeros to the front and not to the 2 parts.
Column 1 - 12345 should be 6 characters 012345
Column 2 - 1 should be 3 characters 001.
The code you showed in returning
000123451 not 012345001 as I am trying to get.[/quote]
I'm sure that you can figure out how to correct the code now that you have an example. That will help you to understand it better.Mon, 09 Sep 2013 14:47:36 GMTLuis CazaresRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492922.aspxit turns out it is not working correctly. It is adding all the zeros to the front and not to the 2 parts.
Column 1 - 12345 should be 6 characters 012345
Column 2 - 1 should be 3 characters 001.
The code you showed in returning
000123451 not 012345001 as I am trying to get.Mon, 09 Sep 2013 14:36:29 GMTJohnny HRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492920.aspxYou're welcome. :-DMon, 09 Sep 2013 14:32:50 GMTSean LangeRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492919.aspxThanks, works great.Mon, 09 Sep 2013 14:18:59 GMTJohnny HRE: concatenate with leading zeroshttps://www.sqlservercentral.com/Forums/FindPost1492915.aspx[quote][b]John.Hagen (9/9/2013)[/b][hr]Hi Everyone
I am creating a view which involved concatenation of 2 int columns.
The data in the columns look like
Column 1 Column 2
1234 1
12345 11
I am trying to get the following output
001234001
012345011
So the first column should have zeros padded to the front to make 6 numbers, the second column should be 3 numbers long with zeros in front. So when added together it is 9 numbers long.
Thanks in advance.
[/quote]
Something like this:
[code]
select right(replicate('0', 9) + cast(Col1 as varchar(9)) + cast(Col2 as varchar(9)), 9)
[/code]Mon, 09 Sep 2013 14:08:52 GMTSean Lange