• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3633
  • Last Modified:

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?
0
tomaz108
Asked:
tomaz108
2 Solutions
 
brejkCommented:
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
 
SteveH_UKCommented:
try

SELECT REPLACE(STR(NUMBER_VARIABLE, 8, 5), ' ', '0') FROM ...
0
 
brejkCommented:
@SteveH_UK: rather - SELECT REPLACE(STR(NUMBER_VARIABLE, 10, 6), ' ', '0')  FROM ...
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
SteveH_UKCommented:
brejk:  doesn't your code produce too many digits?  The author requested xxx.xxxxx?
0
 
brejkCommented:
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
 
Jerryuk007Commented:
@brejk

Just a little note...

Modify your variable to 6.333335...

Then the results become:
006.33333 and 006.33334 ;-)

Jerry
0
 
brejkCommented:
@Jerryuk007

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now