Solved

I need an Identity column in my table that has a letter prefix.....eg z3456,z3457,z3458...etc How can this be achieved in sql server?

Posted on 2004-09-09
14
473 Views
Last Modified: 2006-11-17
I need an Identity column in my table that has a letter prefix.....eg z3456,z3457,z3458...etc How can this be achieved in sql server?

Please note, I am a beginner.

Your help would be greatly appreciated.

Kind Regards

PG
0
Comment
Question by:pgilfeather
  • 3
  • 3
  • 3
  • +3
14 Comments
 
LVL 15

Expert Comment

by:mattisflones
Comment Utility
Why? Make a ordinary identcoloumn and add the Z afterwards..
0
 
LVL 6

Expert Comment

by:OlegP
Comment Utility
1)at table you must field ID1 int identity(1,1)
2)at table you must field ID2 varchar(20)
3) create trigger- which will be update field ID2 AS 'Z'+ID1
 or make ID2 - calculated field as  'Z'+ID1

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Use a normal identitiy column, and create a computed column that concatenates the 'z' with the identity value.

Cheers
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
If you need an auto-incremental filed as a primary, I suggest you stock to

an INT IDENTITY datatype.

Then, if need be, you can create another column (a computed column to get the modified ID in a string format whith the heading 'z'

alter table <YourTable> add ComputedID as 'z' + cast(ID as varchar(10)
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
@angelIII
sorry, simultaneous post ...

@PG
please don't use a trigger for that. No offense to OlegP, but the added value is not worth teh overhead of a trigger
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
3 comments in the same minute ;-)
To add some information:
keeping the 'Z' and the identity in different columns should make searches and joins more performant.
If you have other tables in YOUR database, they should also basically store the identity value and compute the 'z' part apart...
CHeers
0
 
LVL 6

Expert Comment

by:OlegP
Comment Utility
the simplest way use view with calculate ID field to Z+ID
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:pgilfeather
Comment Utility
Hilaire

Can you elaborate a little bit on 'z' + cast(ID as varchar(10)

I know from this that you know how to do it but can you guide me as to how to actually implement this formula?

Kind Regards

PG

0
 
LVL 9

Expert Comment

by:solution46
Comment Utility
Agree with AngelIII

Don't worry about the Z in your tables. Use a straightforward identity column and add the Z whenever you generate any output, or in your front end interface.

s46.
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
Comment Utility
Once you have the "normal" identity column (I assumed it's called ID)
the you just have to run the code below ( using your actual table name instead of <Tabel Name>) to create the computed column,

--note there was a missing parenthesis in my previous csample code
alter table <YourTable> add ComputedID as 'z' + cast(ID as varchar(10))

then you can use it as a normal column

select ComputedID from <YourTable>
0
 

Author Comment

by:pgilfeather
Comment Utility
Oh,

I see where the misunderstanding is happening.

I dont want to generate an ID prefixed with z.

I have two tables that have numeric identity columns Int (4). What I need to do is automatically copy the new ID from one table into the other but with a z prefix.

Hope this makes more sense.

Thanks very much for all your help.

PG
0
 
LVL 6

Expert Comment

by:OlegP
Comment Utility
use trigger or create sp which will be insert new records, receive ID and insert z+ID to another table or ... many varians
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Still we don't understand is WHY you need to 'z' prefix?

Can you post the relevant table structure and explain what you are trying to achieve in plain english
CHeers
0
 

Author Comment

by:pgilfeather
Comment Utility
Hilaire...you get star prize!! Your solution worked great.

Cheers

PG
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

19 Experts available now in Live!

Get 1:1 Help Now