Post reply

Auto increment int with leading zeros

  • March 9, 2018 at 5:08 am

    #344340

    Hello, hopefully I'm asking in the right forum.  If not apologies and please move to correct forum.

    I have an auto increment (identity specification) field/column with the int datatype that I want to default with leading zeros.

    My question is is this possible with int - if so how? Or should I change to another data type?

    Thanks

  • March 9, 2018 at 5:20 am

    #1982596

    Hi mf_connolly,

    You will not be able to prefix the int column data with leading zeros. One way to achieve this would be adding a new calculated varchar field which can prefix with the required zeros to the int field 

    SAMPLE
    create table t (c1 int identity (1,1), c2 AS right('000000000000' + cast(c1 AS VARCHAR(20)), 10),c3 varchar(20))
    insert into t(c3) values ('a'),('b'),('c')
    select * from t
    drop table t

    Hope this helps 🙂

    For computed field limitations and usage please refer
    https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table

  • March 9, 2018 at 6:28 am

    #1982601

    mf_connolly - Friday, March 9, 2018 5:08 AM

    Hello, hopefully I'm asking in the right forum.  If not apologies and please move to correct forum.

    I have an auto increment (identity specification) field/column with the int datatype that I want to default with leading zeros.

    My question is is this possible with int - if so how? Or should I change to another data type?

    Thanks

    You cannot store leading zeros in the INT datatype or in fact any numerical datatype. The only option you have is to store it as a character string.
    😎

    My question is why you need to do this?

Viewing 3 posts - 1 through 2 (of 2 total)

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