Identity (Autonumber)

  • Very new to SQL not sure if this is the right forum or not.

    Question about identity (same colume type as autonumber in access) I am trying to input a number with leading 0's but it keeps taking them out is there a way of keeping them so that I get a number like this 00091 instead of 91?

    Thanks in advance.

    Cheers Pete Thompson

  • identity is related to a numeric datatype so leading zeroes are meaningless.

    You can present them anyway with things like this :

    DECLARE @I INT

    DECLARE @C CHAR(8)

    SET @C = ''

    SET @I = 123

    SET @C = RIGHT(@I+1000000000000000000,8)

    PRINT @C

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • That won't work. The identity property you can only declare on numeric columns like int, or decimal. Leading 0s will be ignored.

    What do you want this for?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • hmpf...I blame it on your bike, alzdba

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • What bike ? (changed the avatar for a couple of minutes to SushiNemo)

    I hope the original poster now understands that the leading zeroes-issue is a presentation issue, so should be tackled at application-presentation level.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I am using this column as a members username, so that I can allow people to add users themselfs with out having to add a username and mess things up.

    This will allow their username to be automatically added.

    Hope you understand this.

    Cheer Pete

  • I'm not sure your request quite makes sense to us yet.

    You can't input values into identity columns (unless you set identity_insert on for that table) - they are generated by SQL Server.  Yet you say you want to "input" these values.  However, even if they are inputted (with identity_insert on) to an identity column, that will be a numeric datatype as the others have pointed out, so the leading zeroes just won't be there, since leading zeroes do not change numeric values.

    Your most recent post sounds to me like you're trying to use identity values as usernames - which can't be right, can it?  Can you clarify this a little more, maybe with a specific example of what you're trying to do?

    Thanks,

    Chris

  • I agree with Chris.

    Imagine everyone on this forum would have usernames like 5409045121009 

    Now being serious, I think you should rethink your strategy.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I also agree that this would make a poor username.  In fact, I don't recommend using identities (or any primary keys) as anything other than an internal reference.  By this, I mean don't let your users know what they are.  They should be totally artificial, but users tend to get attached to numbers. 

    However, if you insist on this strategy, you can accomplish it by adding a second column of type char.  You can then get the value of the identity field and convert it to a char adding the leading zeros.  This can be done in a trigger.

  • Maybe this litle article gets you back on track :

    http://www.sqlservercentral.com/columnists/fkalis/coddsrules.asp

     

    It's a nice refresh Frank -Sharky-  has written to the benifit of all of us.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  •  

    Monday morning

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Indeed

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 12 posts - 1 through 11 (of 11 total)

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