Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

concatenate with leading zeros Expand / Collapse
Author
Message
Posted Monday, September 9, 2013 2:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:23 PM
Points: 39, Visits: 172
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.

Post #1492913
Posted Monday, September 9, 2013 2:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 13,224, Visits: 12,068
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 Moden's 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)
Post #1492915
Posted Monday, September 9, 2013 2:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:23 PM
Points: 39, Visits: 172
Thanks, works great.
Post #1492919
Posted Monday, September 9, 2013 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:00 PM
Points: 13,224, Visits: 12,068
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 Moden's 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)
Post #1492920
Posted Monday, September 9, 2013 2:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:23 PM
Points: 39, Visits: 172
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.
Post #1492922
Posted Monday, September 9, 2013 2:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1492925
Posted Monday, September 9, 2013 2:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:23 PM
Points: 39, Visits: 172
select right(replicate('0', 5) + cast(Col1 as varchar(6))+ replicate('0', 2)+ cast(Col1 as varchar(9)), 9)

gives me the correct values.
Post #1492926
Posted Monday, September 9, 2013 3:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1492929
Posted Monday, September 9, 2013 6:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 3,618, Visits: 5,254
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!
Post #1492961
Posted Monday, September 9, 2013 10:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1492979
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse