concatenate with leading zeros

Question

concatenate with leading zeros

Johnny H

Right there with Babe

Points: 799
More actions
September 9, 2013 at 2:05 pm

#279588

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.

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

John.Hagen (9/9/2013)
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.

Something like this:

select right(replicate('0', 9) + cast(Col1 as varchar(9)) + cast(Col2 as varchar(9)), 9)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Johnny H Right there with Babe Points: 799 More actions · Answer 2

Johnny H

Right there with Babe

Points: 799

September 9, 2013 at 2:19 pm

#1648655

Thanks, works great.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

You're welcome. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Johnny H Right there with Babe Points: 799 More actions · Answer 4

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

John.Hagen (9/9/2013)
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.

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Johnny H Right there with Babe Points: 799 More actions · Answer 6

select right(replicate('0', 5) + cast(Col1 as varchar(6))+ replicate('0', 2)+ cast(Col1 as varchar(9)), 9)

gives me the correct values.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

I'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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

At the risk of being called a contrarian, this can be done without using RIGHT:

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;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

dwain.c (9/9/2013)
At the risk of being called a contrarian, this can be done without using RIGHT:
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;

Perhaps RIGHT is the right way to go though it does take a lot of rows to make a difference.

--===== 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;

Results:

(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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 10

Jeff Moden (9/9/2013)
dwain.c (9/9/2013)
At the risk of being called a contrarian, this can be done without using RIGHT:
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;
Perhaps RIGHT is the right way to go though it does take a lot of rows to make a difference.
--===== 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;
Results:
(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.

Indeed, perhaps your right but two RIGHTs don't make a wrong!

PRINT '========== Another Integer Math Method =========='

SET STATISTICS TIME ON;

SELECT @Bitbucket = RIGHT(1000000000 + C1*1000 + C2, 9)

FROM #TestTable;

SET STATISTICS TIME OFF;

Latest results:

========== 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.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

In the interest of science...

--===== 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;

Results:

========== 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.

========== Integer Math RIGHT(CAST) Method ==========

SQL Server Execution Times:

CPU time = 390 ms, elapsed time = 389 ms.

========== REPLACE(STR) Method ==========

SQL Server Execution Times:

CPU time = 1482 ms, elapsed time = 1472 ms.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

Mine 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.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

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." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 14

Jeff Moden (9/9/2013)
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." 😛

I'm always imagining that, but the imagineering is often easier than the engineering.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St