| Author |
Topic |
|
sinjin67
Yak Posting Veteran
53 Posts |
 Posted - 2008-01-10 : 14:05:41
|
| Greetings all,I have upsized 2 foxpro table to SQL Express.In table 2 called Orphan I have/had a field thatwould autonumber with the letter "U" first.Example would create a AlphaNumber Like "U00001223"In Foxpro this is a easy function, But I have beenunable to find a similiar way to do this in SQLEXPRESS.Foxpro would have you define the field as Unique Number with template defintion of "U"N8In SQL I know you can use Numeric on a field for a unique number but cant find setting to proced number with a "U"Is this doable is in SQL and does anybody have a reference point or example they could share ?Any help would be great and hope my question made sense.. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-01-10 : 14:28:37
|
IDENTITY (data_type [ , seed , increment ] )Check Books online
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2008-01-10 : 14:38:55
|
| First, create an identity column in your table. Then once you have this identity column, you can create a computed column that concatenates the letter 'U' with the identity column that you added to your table.SQL Server Helperhttp://www.sql-server-helper.com |
|
|
|
sinjin67
Yak Posting Veteran
53 Posts |
Posted - 2008-01-10 : 14:55:45
|
| Ok, thanks..Let me try it out.----update---Well after a bit of reading the help file I was able to follow your suggestion and ended up with the following.#1 I setup the identity column#2 Setup the computed column #3 used the following value in the formula portion of computed column ('U'+CONVERT([varchar](10),[GRID],0))Saved it and ran my application against the new setupin each case I got the U# just like I needed.U10001U10002Thanks for getting me in the right direction. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
SuPal
Starting Member
1 Post |
Posted - 2008-02-22 : 08:15:49
|
HI,I am up with the similar kind of problem.I tried doing it the way you suggested but it doesnt work.I have created a Identity column with datatype int and set the Identity sepcification to 'Yes'I have created another column and added the formula for the computed column specification as 'sp'+@[identity] ( so that i can generate values like sp1,sp2..etc..)but it fails..Can you please help me out in this?quote:
Originally posted by sinjin67
Ok, thanks..Let me try it out.----update---Well after a bit of reading the help file I was able to follow your suggestion and ended up with the following.#1 I setup the identity column#2 Setup the computed column #3 used the following value in the formula portion of computed column ('U'+CONVERT([varchar](10),[GRID],0))Saved it and ran my application against the new setupin each case I got the U# just like I needed.U10001U10002Thanks for getting me in the right direction.
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-02-22 : 08:20:35
|
| Did you read the link I posted?MadhivananFailing to plan is Planning to fail |
|
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-02-22 : 08:21:52
|
| 'sp'+@[identity] should be 'sp'+cast([YOUR_IDENTITY_COLUMN] as varchar)"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
AlphaNumeric Function