covert numeric (19,5) to string (10,4)

Question

Post reply

covert numeric (19,5) to string (10,4)

jenniferhu46

SSCrazy

Points: 2990
More actions
February 20, 2006 at 10:39 am

#111262

Hi all,
I have a payrate column in SQL Server 2000 as a numeric (Precision=19, scale=5).
I need to export this value into a text file, the value has to be convert to string value with a fixed 10 precision and 4 decimal point (xxxxx.xxxx)
FOr example : 10 => 00010.0000
8.75 => 00008.7500
How to do that in the select statement (SQL Query)?
Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kevin mann Hall of Fame Points: 3894 More actions · Answer 1

This is what DTS is good for. You can use a vb script on a datapump for that particular column.

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 2

DECLARE

@NumberToModify NUMERIC(19,5)

SET @NumberToModify = 10

--Use your field name instead of @NumberToModify

SELECT REPLICATE('0',5-(LEN(@NumberToModify)-6))+CONVERT(VARCHAR(24),@NumberToModify)

HTH:

Mark

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Check out "STR function" in BOL

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Jennifer,

I agree with Serqiy...

SELECT STR(yourcolumn,10,4)

FROM yourtable...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 5

YOU CAN USE THE FOLLOWING QUERY :--

select replace(str(convert(varchar(14),convert(numeric(19,5),58)),10,4),' ','0') a

YOU CAN USE IN PLACE OF '58' YOUR COLUMN

OK

BYE!!!