| Author |
Topic |
|
spid3r
Starting Member
4 Posts |
 Posted - 2008-04-10 : 08:32:53
|
| Hi, I am trying to figure out and hope u guys help me. I need a only 6 digit ID only eg 55 = 000055405 = 0004055544 = 005544How i can do it in SQL server and which formula/datatype i must use?Deaf can Do anything, except hear |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-10 : 08:34:25
|
| [code]right('000000'+ID, 6)[/code]provided ID is varchar.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
spid3r
Starting Member
4 Posts |
Posted - 2008-04-10 : 08:40:15
|
| Thank, If it about Query, but i dont want that. i just want change in Design Table where i can change datatype format etc so there a column Tabshown Description to Collation. Isn't the above answer will work on it?Deaf can Do anything, except hear |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-10 : 08:40:37
|
quote:
Originally posted by harsh_athalye
right('000000'+ID, 6)provided ID is varchar.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
You need only five zeroes provided ID is not empty  MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-10 : 08:41:22
|
quote:
Originally posted by spid3r
Thank, If it about Query, but i dont want that. i just want change in Design Table where i can change datatype format etc so there a column Tabshown Description to Collation. Isn't the above answer will work on it?Deaf can Do anything, except hear
Leave it as it is. When you want to show data in the front end application, format the number thereMadhivananFailing to plan is Planning to fail |
|
|
|
spid3r
Starting Member
4 Posts |
Posted - 2008-04-10 : 08:51:05
|
| Thank, to make you clearIn Design Table i have PatientID and use int for Datatype. So i dont want change any datatype cos it is project. So the client want PatientID in only 6 digit number like i said first topic above 55 = 000055405 = 0004055544 = 005544So please help. thank Deaf can Do anything, except hear |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-10 : 08:55:53
|
quote:
Originally posted by spid3r
Thank, to make you clearIn Design Table i have PatientID and use int for Datatype. So i dont want change any datatype cos it is project. So the client want PatientID in only 6 digit number like i said first topic above 55 = 000055405 = 0004055544 = 005544So please help. thank Deaf can Do anything, except hear
Did you try the queries suggested?Also, where do you want to show data?MadhivananFailing to plan is Planning to fail |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-10 : 09:07:24
|
quote:
Originally posted by spid3r
Thank, to make you clearIn Design Table i have PatientID and use int for Datatype. So i dont want change any datatype cos it is project. So the client want PatientID in only 6 digit number like i said first topic above 55 = 000055405 = 0004055544 = 005544So please help. thank Deaf can Do anything, except hear
If patientID is int, why you want to prefix it with 0s? It won't display the way you want, no matter what you do.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
spid3r
Starting Member
4 Posts |
Posted - 2008-04-10 : 09:09:56
|
quote:
Originally posted by harsh_athalye
quote:
Originally posted by spid3r
Thank, to make you clearIn Design Table i have PatientID and use int for Datatype. So i dont want change any datatype cos it is project. So the client want PatientID in only 6 digit number like i said first topic above 55 = 000055405 = 0004055544 = 005544So please help. thank Deaf can Do anything, except hear
If patientID is int, why you want to prefix it with 0s? It won't display the way you want, no matter what you do.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
Because the Client want display with 6 digit. If it wont display then thank :)Deaf can Do anything, except hear |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-10 : 09:32:31
|
The add the leading zero at the client application.Or, make a calculated column with leading zeros, and return that column to client instead.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
6 Digit Number only