none
How to add 0's before the string in MS SQL server? RRS feed

  • Question

  • Hi,

    I need to add 0's to the left.

    my rule says that "loaded in this format: '002' + Equivalent SE PLU (5 digits) + '00000' + DV "

    Please provide the command which is equivalent to LPAD in oracle.Thanks in advance.

    • Moved by Dan GuzmanMVP Wednesday, April 16, 2014 11:56 AM T-SQL question
    Wednesday, April 16, 2014 11:40 AM

Answers

  • Padding is done on SQL Server using REPLICATE function

    Link: 

    http://technet.microsoft.com/en-us/library/ms174383.aspx

    Wednesday, April 16, 2014 12:00 PM
  • I have another point of view on this. Lets not "format" the data in DB. I think this is a presentation requirement (if not please ignore this reply) & so must be formatted / presented as required in the presentation layer.

    hope this helps!

    Wednesday, April 16, 2014 12:06 PM
  • I assume the "Equivalent SE PLU" column is an integer and you want to add leading zeros to make 5 digits?  One method is to concatenate leading zeros to the integer value casted as varchar, and then trim to the desired length.  For example:

    RIGHT('0000' + CAST(PLU AS varchar(10)), 5)

    I'll move this thread to the Transact-SQL forum, which is more appropriate for this question.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, April 16, 2014 11:56 AM

All replies

  • I assume the "Equivalent SE PLU" column is an integer and you want to add leading zeros to make 5 digits?  One method is to concatenate leading zeros to the integer value casted as varchar, and then trim to the desired length.  For example:

    RIGHT('0000' + CAST(PLU AS varchar(10)), 5)

    I'll move this thread to the Transact-SQL forum, which is more appropriate for this question.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, April 16, 2014 11:56 AM
  • Padding is done on SQL Server using REPLICATE function

    Link: 

    http://technet.microsoft.com/en-us/library/ms174383.aspx

    Wednesday, April 16, 2014 12:00 PM
  • I have another point of view on this. Lets not "format" the data in DB. I think this is a presentation requirement (if not please ignore this reply) & so must be formatted / presented as required in the presentation layer.

    hope this helps!

    Wednesday, April 16, 2014 12:06 PM
  • Yes, i want to compare the source and target tables.

    so to compare source with target(using EXCEPT) i need to LPAD 0's in the left for the source table.

    Wednesday, April 16, 2014 12:27 PM
  • Hello,

    Did you refer to the function as Dan post above? It equivalent to LPAD in oracle and should meet your requirement.

    If there are any other question, please post the DDL of the source and target table, and sample data for further analysis.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, April 23, 2014 8:48 AM
    Moderator
  • if the source and target columns are the same type and drawn from the same domain, then why don't they look the same? Where is the CHECK() in the DDL to assure this? Your narrative (no data, no DDL, no Netiquette) implies that one is INTEGER and one is CHAR(n); that would be a serious design error. 

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

    Wednesday, April 23, 2014 1:21 PM
  • Padding with leading zeroes:

    http://sqlusa.com/bestpractices2005/padleadingzeros/

    > Lets not "format" the data in DB. I think this is a presentation requirement (if not please ignore this reply) & so must be formatted / presented as required in the presentation layer.

    That is not practical in most IT shops. Frequently, you - the database programmer - are the presentation layer. Your boss may say: "Email ASAP the chief accountant a .... report with field X in 8 leading zeros format as Excel attachment."



    Kalman Toth Database & OLAP Architect T-SQL Scripts at sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Wednesday, April 30, 2014 5:45 PM
    Moderator
  • Hi JKumar,

    You can use leading zero padding solution with Right() and Replicate() functions

    Here is a function built on top of these SQL functions named udfLeftSQLPadding where you can find the source codes here in this SQL tutorial SQL Pad Leading Zeros

    select dbo.udfLeftSQLPadding('123',5,'0')
    
    Output is "00123"

    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Thursday, May 1, 2014 1:23 PM
    Moderator