concatenate with leading zeros

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

  • 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/

  • Thanks, works great.

  • 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/

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

  • 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
  • select right(replicate('0', 5) + cast(Col1 as varchar(6))+ replicate('0', 2)+ cast(Col1 as varchar(9)), 9)

    gives me the correct values.

  • 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
  • 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

  • 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)

  • 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

  • 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)

  • 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

  • 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)

  • 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

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

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