none
how to pad left and right in sql select statement RRS feed

  • Question

  • Hi,

    I need to pad two zeros at left side of the vale and remaining zeros right side of the value in select results could any one help me with this.

    EX: if the out put value is 20, we need to add two zeros to the right side and remaining zeros to the left side total digits should be 7

    the result should look like 0002000. Please help me with this.


    Saturday, April 27, 2013 12:07 PM

Answers

All replies

  • The following blog is on padding:

    http://www.sqlusa.com/bestpractices/pad/

    You can use the REPLICATE function to produce zeros, LEFT, RIGHT & SUBSTRING functions cut it to the right size.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle:  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    Saturday, April 27, 2013 12:34 PM
    Moderator
  • Declare @Sample Table(MyData int);
    Insert @Sample(MyData) Values (5), (20), (14567);
    Select Right('00000' + Cast(MyData As varchar(5)) + '00', 7)
    From @Sample;

    Tom
    Saturday, April 27, 2013 1:02 PM
  • Something like this?

    declare @localvariable as int=20,@output varchar(7)
    
    set @output =stuff( cast (@localvariable as varchar(7)),1,0,'00')+ REPLICATE('0',7-len(@localvariable)-2)
    
    select @output


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Saturday, April 27, 2013 2:03 PM
  • >> I need to pad two zeros at left side of the value and remaining zeros right side of the value in select results could any one help me with this. <<

    The kludge is simply string functions. 
    SUBSTRING (('00' + vague_value + '000000'), 1, 7)

    But a kludge is not a solution. You need to fix the DDL that you did not post (read the forum Netiquette, please). I will guess this is a string and not an integer. You were no help to us. 

    CREATE TABLE Rude_Posters
    (vague_value CHAR (7) NOT NULL
       CHECK (vague_value LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
     ..);



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, April 27, 2013 7:49 PM
  • Hi,

    Try this:

    DECLARE @pad varchar(7) = '20'

    SET @pad=(select left('00'+@pad,7) )+(select right('00000'+@pad,7))
    print @pad

    Regards.


    blue world

    Sunday, April 28, 2013 3:46 AM
  • u can also try this:

    DECLARE @pad varchar(7) = '20'

    SET @pad=(select '00'+ @pad+REPLICATE('0',5) )
    print @pad


    blue world

    Sunday, April 28, 2013 4:32 AM
  • Something like this?

    declare @localvariable as int=20,@output varchar(7)
    
    set @output =stuff( cast (@localvariable as varchar(7)),1,0,'00')+ REPLICATE('0',7-len(@localvariable)-2)
    
    select @output


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    One more simple approach, with less expressions and conversions,

    declare @localvariable as int=28988,@output varchar(7)
    
    set @output =stuff('0000000' ,3,0,cast (@localvariable as varchar(7)))
    
    select @output


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, April 28, 2013 6:12 PM