Preserve 4 decimal places when converting to TEXT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Preserve 4 decimal places when converting to TEXT

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2010-01-21 : 17:14:50

Hey guys,

I'm trying to generate an output file with pipes (|) between the column names and the field values. Please see below:

PRINT 'ITEM_NO|ORDER_PRICE

SELECT
ITEM_NO, + '|' +
CONVERT(VARCHAR, ITEM_VEND_PKG.PRICE) AS ORDER_PRICE
FROM ...

The script above generates the following output:

ITEM_NO|ORDER_PRICE
1234|3.03

I need the script to display up to 4 decimal places for the ORDER_PRICE field like this:

ITEM_NO|ORDER_PRICE
1234|3.0345

I have to convert ORDER_PRICE to VARCHAR or else the script fails because of the concatenation of the pipe and the field values (+ '|' +).

I tried the code below but it does not work:

CAST(CAST(ORDER_PRICE AS DECIMAL(6,4)) AS VARCHAR)

Anybody have any sueggestions?

Thank you in advanced!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 17:26:47

What datatype is order_price in your table?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 17:30:46

this works, so what is the value in the database?
declare @price decimal(6,4)
select @price=3.0345
select @price
select '1234'+'|'+convert(varchar(6),@price)

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-21 : 18:09:42

or use bcp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-21 : 18:31:33

SELECT '|' + LTRIM(STR(Col1, 20, 4)) + '|'
FROM Table1

N 56°04'39.26"
E 12°55'05.63"

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2010-01-21 : 23:12:56

Hey Webfred, sorry I forgot to mention that the PRICE column is MONEY datatype.

Thank you all for your quick replies!! I will try your suggestions when I'm in the office tomorrow.

Cheers!

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 01:36:47

For MONEY datatype

CONVERT(varchar(20), @price, 2)

will give you 4 d.p. Adjust the width of varchar(20) as appropriate.

Are you CERTAIN that "|" does not occur anywhere in your data?

I am frequently asked to use "pipe" as a delimiter by third parties, invariably the data contains that character somewhere. We have email addresses in our database that contain "|" - presumably Geeks who are hoping that will break some data tranfer at some point ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources