|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
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: Show quote > > 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. Show quote "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: >> >> 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.... 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(1000000+@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 -- Show quote-oj "Francisco" <fvice***@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... >> 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.... > > 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: Show quote > > 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: > >> > >> 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....  |
|||||||||||||||||||||||
Other interesting topics