Formatting a Number

[PrakashN]

Hi,

I want format a number CU00001(ie 1 to 00001)
please help me..

thanking you

[gpl]

Hi,

I want format a number CU00001(ie 1 to 00001)
please help me..

thanking you

Assuming that you always want a 5-digit number, prepend your value with 5 0s then take the rightmost 5 characters, something like

Select Right('00000' + Convert(varchar(5), @MyVar), 5)

If you are going to use this multiple times, then wrap it up in a handy function
Graham

[amitpatel66]

Hi,

I want format a number CU00001(ie 1 to 00001)
please help me..

thanking you

Could you provide sample data before formatting and how you want after formatting?

[PrakashN]

Could you provide sample data before formatting and how you want after formatting?

Hi,
I got the answer. Thank you for ur repling. I am new to stored procedure..

Thank you

[PrakashN]

Could you provide sample data before formatting and how you want after formatting?

Hi,
I got the answer. Thanks for repling. I am new to stored procedure..


Here is the simple procedure which is used to add a customerdetails

Create procedure [dbo].[AddCustomer]
@FName nvarchar(50),
@LName nvarchar(30),@A dd1 nvarchar(50),
@Add2 nvarchar(50),@C ity nvarchar(50),
@State nvarchar(20),@P incode nvarchar(6),
@Phone nvarchar(25),@C ountry nvarchar(20),
@Email nvarchar(50),@T ype nvarChar(1),
@CardNo nvarchar(50)

as
begin
declare
@id nvarchar(12);
-- if customerID exists means get max value and increment that value
-- else add First value
if (select count(*) from CustomerDetails )>0
begin;
select @id=max(right(I D,5)) from CustomerDetails
set @id=right(@id,5 )
set @id=cast(@id as float)+1
set @id='CU'+Right( '00000' + Convert(varchar (5), @id), 5)
end;
else
set @id='CU00001'


Insert into CustomerDetails values (@id,@FName,@LN ame,@Add1,@Add2 ,@City,@State,@ Pincode
,@Phone,@Countr y,@Email,@Type, @CardNo)

end


Thank you