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