Avatar of Michael Franz
Michael Franz
 asked on

Formatting in SQL

I have a number that keeps coming across in the report as 123456789, where I want $1,234,567 please help. The field name is Sold
Microsoft SQL Server

Avatar of undefined
Last Comment
Michael Franz

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mimran18

if you are using sql server 2012
here is the solution.

http://raresql.com/2012/09/19/sql-server-2012-string-function-format/
Simone B

Right click on the field in the layout, and select Properties. Go to the format tab, and select the ellipses next to Format code. Select Currency.

Format
mimran18

SELECT '$' + CONVERT(varchar, CAST(987654321 AS money), 1)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
DBARider

this is a formatting/display issue.. in a report. Change the report definition to display as currency.  Shouldn't be up to the DB to provide this type of formatting.
venuskarur

CREATE THIS FUNCTION




SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_NumberToCurrency]
(    
  @InNumericValue NUMERIC(38,2)
 ,@InFormatType   VARCHAR(10)
)
RETURNS VARCHAR(60)
AS
BEGIN
DECLARE     @RetVal     VARCHAR(60)
            ,@StrRight  VARCHAR(5) 
            ,@StrFinal  VARCHAR(60)
            ,@StrLength INT
                 
SET @RetVal = ''
SET @RetVal= @InNumericValue 
SET @RetVal= SUBSTRING(@RetVal,1,CASE WHEN CHARINDEX('.', @RetVal)=0 THEN LEN(@RetVal)ELSE CHARINDEX('.',@RetVal)-1 END)

IF(@InFormatType = 'US')
BEGIN
SET @StrFinal= CONVERT(VARCHAR(60), CONVERT(MONEY, @RetVal) , 1)
SET @StrFinal= SUBSTRING(@StrFinal,0,CHARINDEX('.', @StrFinal))
END

ELSE
IF(@InFormatType = 'IND')
BEGIN
SET @StrLength = LEN(@RetVal)
IF(@StrLength > 3)
BEGIN
      SET @StrFinal = RIGHT(@RetVal,3)         
      SET @RetVal = SUBSTRING(@RetVal,-2,@StrLength)
      SET @StrLength = LEN(@RetVal)
      IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)
            BEGIN
            SET @StrFinal = @RetVal + ',' + @StrFinal
            END
      WHILE LEN(@RetVal) > 2
            BEGIN
            SET @StrRight=RIGHT(@RetVal,2)               
            SET @StrFinal = @StrRight + ',' + @StrFinal
            SET @RetVal = SUBSTRING(@RetVal,-1,@StrLength)
            SET @StrLength = LEN(@RetVal)
            IF(LEN(@RetVal) > 2)
            CONTINUE
            ELSE
            SET @StrFinal = @RetVal + ',' + @StrFinal
            BREAK
            END
      END
      ELSE
      BEGIN
            SET @StrFinal = @RetVal
      END
   
END
SELECT @StrFinal = ISNULL(@StrFinal,00)
RETURN @StrFinal
END

Open in new window




GET DESIRED OUTPUT BY

SELECT '$' + [dbo].[udf_NumberToCurrency] (1234567890, 'us')

Open in new window

Anthony Perkins

DBARider,

Shouldn't be up to the DB to provide this type of formatting.
Could not agree more.

venuskarur,

Perhaps it is just me, but isn't that a tad complicated for something that can be achieved easily with the CONVERT function.

If you insist on creating a function than create one using CLR or suffer the consequences.


Now whatever happened to the author...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Michael Franz

ASKER
thanks for the help. sorry for the delay. out of office.