SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


concatenate with leading zeros


concatenate with leading zeros

Author
Message
Johnny H
Johnny H
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 181
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62625 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Johnny H
Johnny H
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 181
Thanks, works great.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62625 Visits: 17959
You're welcome. :-D

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Johnny H
Johnny H
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 181
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
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41981 Visits: 19829
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
Johnny H
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 181
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
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41981 Visits: 19829
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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17829 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215202 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search