SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Preserve 4 decimal places when converting to TEXT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlslick
Yak Posting Veteran

83 Posts

Posted - 01/21/2010 :  17:14:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 01/21/2010 :  17:26:47  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 01/21/2010 :  17:30:46  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 01/21/2010 :  18:09:42  Show Profile  Visit russell's Homepage  Reply with Quote
or use bcp
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 01/21/2010 :  18:31:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT '|' + LTRIM(STR(Col1, 20, 4)) + '|'
FROM Table1



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 01/21/2010 :  23:12:56  Show Profile  Reply with Quote
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!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/22/2010 :  01:36:47  Show Profile  Reply with Quote
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 ...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000