Formatting in SQL

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

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

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.

SELECT '$' + CONVERT(varchar, CAST(987654321 AS money), 1)

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.

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

Select allOpen in new window

GET DESIRED OUTPUT BY

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

Select allOpen in new window

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...

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