# convert numeric to varchar

Hi!
I want to convert numeric(decimal) value into varchar. format must be like this: xxx.xxxxx. Here are some examples:
1.250000023 => 001.25000
6.666666666 => 006.66667
33.33333333 => 033.33333
100.0000000 => 100.00000

all numbers are between 0.00000000 and 100.00000000
In Oracle is like this:
select substr(to_char(NUMBER_VARIABLE,'099.99999'),2) from....
How to do it in Sql Server 2000?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
declare @dec decimal(38,10)
set @dec = 6.666666666
select right(replicate('0',10) + cast(cast(@dec as decimal(9,6)) as varchar(9)), 10)
0
Commented:
try

SELECT REPLACE(STR(NUMBER_VARIABLE, 8, 5), ' ', '0') FROM ...
0
Commented:
@SteveH_UK: rather - SELECT REPLACE(STR(NUMBER_VARIABLE, 10, 6), ' ', '0')  FROM ...
0
Commented:
brejk:  doesn't your code produce too many digits?  The author requested xxx.xxxxx?
0
Commented:
Steve: You are right, my code produces one digit more :-) Thanks.

So finally:

declare @dec decimal(38,10)
set @dec = 6.666666666
SELECT REPLACE(STR(@dec, 9, 5), ' ', '0')
select right(replicate('0',10) + cast(cast(@dec as decimal(9,5)) as varchar(9)), 9)

Results of both SELECTs are 006.66667.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
@brejk

Just a little note...

Then the results become:
006.33333 and 006.33334 ;-)

Jerry
0
Commented:
@Jerryuk007

Thanks for the valuable note. So the author can choose the method he/she prefers ;-)
0
Author Commented:
Thank you very much. Haribol!
0
Commented: