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),@Add1 nvarchar(50),
@Add2 nvarchar(50),@City nvarchar(50),
@State nvarchar(20),@Pincode nvarchar(6),
@Phone nvarchar(25),@Country nvarchar(20),
@Email nvarchar(50),@Type 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(ID,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,@LName,@Add1,@Add2,@City,@State,@Pinco de
,@Phone,@Country,@Email,@Type,@CardNo)

end


Thank you