SQL Identity with Leading Padded Zeros

SQL Identity with leading padded zeros

If you want to display your number column with leading zeros, just pad it in your SELECT statement. It's a number, it will NOT store with leading zeros as an integer.

SELECT RIGHT('00000' + CAST([number] AS varchar(5)) , 3)
FROM IdentityTest

The 3 is the number of characters you want total in the output display.

SQL Identity with leading padded variable number of zeros

In the end it was done in linq to sql, storing the 'Pad Length'

/// <summary>
    /// Calculate the amount of padding needed for this object
    /// </summary>
    /// <returns></returns>
    private static int _padLength = -1;
    private static int PadLength()
    {
        if (_padLength < 0)
        {
            Aurora.Data.AuroraDataContext db = new AuroraDataContext();
            _padLength = (from r in db.KpiForms select r.Id).Max().ToString().Length;
        }

        return _padLength;
    }

    /// <summary>
    /// Returns the display value for combo boxes
    /// </summary>
    public string DropDownDisplayTitle
    {
        get
        {
            if (this.Id <= 0) return 0.ToString().PadLeft(PadLength(), '0') + " " + this.Title ;
            return this.Id.ToString().PadLeft(PadLength(), '0') + " " + this.Title ;
        }
    }

And that's that, it works, and performance will just have to suffer on this one.

Formatting Numbers by padding with leading zeros in SQL Server

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

SQL Server ID Starting with Zero's

This is no datatype that will pad leading 0s. That type of thing must be done with a character datatype. But you can accomplish this easily.

Also please be sure to always specify the length of varchar data. https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length

create table MCE_IDTest_av(
invoice_id_num int not null Identity(100076,1) primary key,
invoice_id as ('MCE' + right('00000000' + CONVERT(varchar(10), invoice_id_num), 7)),

);

Concatenation Breaks Numbers with Padded Zeros in SQL Server

I think this does what you want:

convert(varchar(6), a.[Fiscal Year] * 100 + a.[Fiscal Month])

SQL Server Pad With Leading Zeroes

You can use RTRIM, hope it will help

select right('0000000000000' + RTRIM(sum(cast('656311.22' as decimal(13,2)))),13)

Add leading zeros after specific character

You can do this with a bunch of string manipulations. Something like this:

select right(replicate('0', 6) + left(val, charindex('/', val)), 7) +
       right(replicate('0', 4) + right(val, charindex('/', reverse(val)) - 1), 4)

Unfortunately, SQL Fiddle just isn't returning this morning, so I can't fully test this.

EDIT: Fixed missing parenthesis

T-SQL Pad a nvarchar number with leading character and zeros for a 9 character format A00000012

Using a bit of old school left padding for T-SQL:

select Id, 'A'+RIGHT(CONCAT(REPLICATE('0',8),Id),8) as PaddedId
from YourTable;

SQL Server - Prefix and zero padding ID column

My proposition for your columns :

• InvoiceID : integer not null with autoincrement (sequence are not necessary, SQL Server do the job)

• Prefix: varchar(10) not null FK on PREFIXTABLE

• InvoiceID and Prefix are the primary key

• InvoiceNumber : computed and persisted column = Prefix + RIGHT('0000000'+ InvoiceID,7)

• Add an index on InvoiceNumber

With my proposition you can :

1. Change prefix if necessary
2. You have integrity on prefix
3. You have integrity on your complet key and keep the really primary key
4. You have not to recalculate the complete key
5. In case of data recovery, you can make jumps of sequences to your keys or simply choose another prefix
6. You can add a description into your PREFIX table for explain your prefix (for futur developper by example)

---

Related Topics