Format

I have a number in a table. By example: 87
I need change this a varchar but with format, by example '00000087'
I need to make this change in a procedure...
Please help me!!!!
In oracle I used TO_CHAR!!!!!!! It was easy...
Thanks...This is best done in the client application (I just had to say it).
If you need it in T-SQL, then there are several solutions. One of them
is this:
Declare @.n int
Set @.n=87
SELECT Replicate('0',8-LEN(CAST(@.n AS varchar(11))))+CAST(@.n AS
varchar(11))
Another is this:
Declare @.n int
Set @.n=87
SELECT Right('00000000'+CAST(@.n AS varchar(11)),8)
HTH,
Gert-Jan
Francisco wrote:
> I have a number in a table. By example: 87
> I need change this a varchar but with format, by example '00000087'
> I need to make this change in a procedure...
> Please help me!!!!
> In oracle I used TO_CHAR!!!!!!! It was easy...
> Thanks...|||Thanks.
Now, My question is I am using Primary Keys like '00000000' .
In my procedure I need add one for the next id.
What is the best option for SQL Server? CHAR o INT?
Thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> escribi en el mensaje
news:4310D9B4.388ED320@.toomuchspamalready.nl...
> This is best done in the client application (I just had to say it).
> If you need it in T-SQL, then there are several solutions. One of them
> is this:
> Declare @.n int
> Set @.n=87
> SELECT Replicate('0',8-LEN(CAST(@.n AS varchar(11))))+CAST(@.n AS
> varchar(11))
> Another is this:
> Declare @.n int
> Set @.n=87
> SELECT Right('00000000'+CAST(@.n AS varchar(11)),8)
> HTH,
> Gert-Jan
>
> Francisco wrote:|||Here is an example of generating your own pk (using some undoc/unsupported
trick).
-- dynamic pk gen
use tempdb
go
create table seed(i int)
insert seed values(0)
go
create proc getval
as
begin
set nocount on
declare @.i int
update seed
set @.i=i=i+1
select convert(char(4),getdate(),12)+right(1000
000+@.i,6) as [i]
end
go
create function dbo.pkgen()
returns char(10)
as
begin
return(select i from openquery(your_server_name,'exec
tempdb..getval;commit')x)
end
go
create table t(pk char(10) primary key default dbo.pkgen(),i int)
go
insert t(i) values(10)
insert t(i) values(20)
insert t(i) values(30)
select * from t
go
go
drop table t
drop function dbo.pkgen
drop proc getval
drop table seed
-oj
"Francisco" <fvicente@.terra.com> wrote in message
news:OtqjrJ1qFHA.2604@.TK2MSFTNGP14.phx.gbl...
> Thanks.
> Now, My question is I am using Primary Keys like '00000000' .
> In my procedure I need add one for the next id.
> What is the best option for SQL Server? CHAR o INT?
> Thanks.
>
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> escribi en el mensaje
> news:4310D9B4.388ED320@.toomuchspamalready.nl...
>|||I would only consider two options:
1) If you want the system to automatically generate the key (a surrogate
key), then you could simply use an Identity column (int)
... MyKey int not null IDENTITY PRIMARY KEY
2) If you want a natural key or at least have some influence and
repeatability in assigning the key, then choose whatever format you
like. Have you client (application) assign the key. If you are planning
on using a number with leading zeros, and it is always fixed length,
then there is no use to start messing with formatting functions. You can
simply store it in a character column, and enforce the format with a
constraint
... MyKey char(8) not null PRIMARY KEY
, CONSTRAINT CK_MyTable_MyKeyFormat
CHECK ( Len(MyKey)=8
AND MyKey NOT LIKE '%[^0-9]%' )
Although the code that oj posted is very interesting and all, I could
not recommend this unnecessary complexity.
HTH,
Gert-Jan
Francisco wrote:
> Thanks.
> Now, My question is I am using Primary Keys like '00000000' .
> In my procedure I need add one for the next id.
> What is the best option for SQL Server? CHAR o INT?
> Thanks.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> escribi en el mensaje
> news:4310D9B4.388ED320@.toomuchspamalready.nl...