Solved

# Select Format Decimal & Currency

Posted on 2011-10-21
209 Views
Hello,

I have the following Stored Procedure....

``````CREATE PROCEDURE [dbo].[LocalRetainerAverage]
(
@Locality int
)
AS
BEGIN
SET NOCOUNT ON;
Select
AVG(RetainerValue) As Retainer,
AVG(FeeCommission) AS FeeCommission,
AVG(TotalRetainer) AS Total
From dbo.RetainerSurvey
Where Locality = @Locality
END

GO
``````

Which is producing results like -

20333.333333

Ideally what I want to do is have the resuilts like

£20333.33

So add the £ and only show 2 characters after the period...

Appreciate any help you can offer -

Thank you
0
Question by:garethtnash

LVL 23

Accepted Solution

``````DECLARE @INPUT FLOAT
SET @INPUT = 20333.333333
select '£' + CAST(CONVERT(money, ROUND(@INPUT , 2)) AS VARCHAR(10))
``````
0

LVL 8

Expert Comment

Personally I'd prefer to leave the formatting to the pertinent app or report generator etc. but if you insist on doing it within our query/stored proc, one possible method is to use Str() Function. It takes three arguments(the number, the number total characters to display, and the number of decimal places to display. For instance assuming you select the required value in a variable called @MyVar, you can use the following to get the result.
SELECT '£'+ STR(@MyVar, 10, 2)
0

LVL 6

Assisted Solution

SELECT '£'+ ltrim (STR(20333.333333, 10, 2));
SELECT '£'+ STR(20333.333333, 10, 2);
0

LVL 6

Expert Comment

``````DECLARE @v2 DECIMAL (36,10)
SELECT @v2 = 13243543.56565656

SELECT '£ '+ CONVERT(VARCHAR,CONVERT(MONEY,@v2),1) --13,243,543.57
--Formatted with commas
``````

http://www.tek-tips.com/faqs.cfm?fid=6157
0

LVL 6

Assisted Solution

``````declare @v money
select @v = 1322323.6666

select '£ '+ convert(varchar, @v, 0)  --1322323.67
-- Rounded but no formatting

select '£ '+ convert(varchar, @v, 1)    --1,322,323.67
-- Formatted with commas

select '£ '+ convert(varchar, @v, 2)    --1322323.6666
-- No formatting
``````

http://www.tek-tips.com/faqs.cfm?fid=6157
0

Author Comment

Hi Guys,

Thanks for all the responses, and apologies for the delayed response. One last question, what about if I wanted to round the number so that -

1,322,323.67
becomes -
1,322,324

thanks
0

Author Closing Comment

thank you
0

LVL 6

Expert Comment

>> One last question, what about if I wanted to round the number so that -
>> 1,322,323.67
>> becomes -
>> 1,322,324

``````declare @v money
set @v = round (1322323.67, 0) -- 1,322,324
select '£ '+ convert(varchar, @v, 1);
``````

0

## Join & Write a Comment Already a member? Login.

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

#### 734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!