Padding Function

Posted on 2005-04-12
Last Modified: 2008-10-07
Hello all,

Kind of new at this SQL stuff and I'm needing to pad some values when I run a query.  Does anyone know of a good way to pad values in a query via a function or something?  Also I need the pad to be with 0's with my data right justified. (0's on the left)

Question by:thcit
    LVL 23

    Expert Comment

    To pad a value to 4 digits:

    DECLARE @intNumber INTEGER
    SET @intNumber = 1

    SELECT RIGHT('0000' + CAST(@intNumber AS VARCHAR), 4)
    LVL 10

    Accepted Solution


    replace(str(yourNumber, 8), ' ', '0')

    this will create a string 8 chars long left padded with 0s
    LVL 34

    Expert Comment

    by:Brian Crowe
    CREATE FUNCTION PadLeft(@text varchar(1000), @fillchar char(1), @totallength int)
    RETURNS varchar(1000)
    WHILE LEN(@text) < @totallength
         SET @text = @fillchar + @text
    RETURN @text
    LVL 3

    Author Comment

    Thanks Rob, that was exactly what I needed.  Functional and lightweight.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now