| Author |
 Topic  |
|
|
sqlslick
Yak Posting Veteran
62 Posts |
 Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 01/21/2010 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 01/21/2010 : 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
USA
5037 Posts |
Posted - 01/21/2010 : 18:09:42
|
| or use bcp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 01/21/2010 : 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
62 Posts |
Posted - 01/21/2010 : 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
United Kingdom
22191 Posts |
Posted - 01/22/2010 : 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 ... |
|
|
| |
Topic |
|
Preserve 4 decimal places when converting to TEXT
