Format numeric values to language specific format

CREATE FUNCTION dbo.fn_ConvertNumber  (@dblValue FLOAT, 
       @intLCID INT, 
       @tintNoDecimals TINYINT)
RETURNS VARCHAR(50)


/*
TSM, 02/07/2004,
Visit: www.sqlscripter.com

Description:
------------
This function converts a float value to a language specific
numericformat. The result is a converted varchar value.

Parameter:
----------

@dblValue = Float value

@intLCID = 
  Result Example
  --------------
        1031  = Germany  1.000,50
   2057  = British English  1,000.50
 1033  = American  1,000.50
 1040  = Italian  1.000,50
 1036  = France  1 000,50
 1029  = Spain  1 000,50

@tintNoDecimals = Number of decimals


Calling Examples: 
-----------------

SELECT dbo.fn_ConvertNumber(1500.555, 1031, 2) AS Example = 1.500,56
SELECT dbo.fn_ConvertNumber(1500.555, 2057, 2) AS Example = 1,500.56
SELECT dbo.fn_ConvertNumber(1500.555, 1036, 2) AS Example = 1 500,56

*/
AS
BEGIN

   DECLARE 
@strResult VARCHAR(50),
@strTmp VARCHAR(50),
@dbl FLOAT,
        @tintDecPos TINYINT,
        @tintNoOfThDel TINYINT,
        @strDecDelimiter CHAR(1),
@strThDelimiter CHAR(1),
        @strDecValue VARCHAR(10),
        @strIntValue VARCHAR(40),
        @tintWhile TINYINT,
        @blnIsMinus BIT


   -- Defaults
   SET @tintWhile = 0
   SET @tintNoOfThDel = 0
   SET @blnIsMinus = 0    

   -- Read incoming value
   SET @dblValue = ISNULL(@dblValue, 0)

   -- Init negative value
   IF SIGN(@dblValue) < 0 
      SET @blnIsMinus = 1  -- We have a negative value

   -- Calculate and convert to string 
   SET @strResult = STR(@dblValue, 30, @tintNoDecimals)
   SET @strResult = LTRIM(RTRIM(@strResult))

   -- In case of a negative value, cut the "-"
   IF @blnIsMinus = 1 
      SET @strResult = SUBSTRING(@strResult, 2, LEN(@strResult) - 1)

   -- Init the requested format
   IF @intLCID = 1031 OR @intLCID = 1040 BEGIN
      -- Format: 1.000,50
      SET @strDecDelimiter = ','-- Decimal Delimiter
      SET @strThDelimiter = '.'-- Thousand Delimiter
   END

   IF @intLCID = 2057 OR @intLCID = 1033 BEGIN
      -- Format: 1,000.50
      SET @strDecDelimiter = '.'-- Decimal Delimiter
      SET @strThDelimiter = ','-- Thousand Delimiter
   END
  
   IF @intLCID = 1036 OR @intLCID = 1029 BEGIN
      -- Format: 1 000,50
      SET @strDecDelimiter = ','-- Decimal Delimiter
      SET @strThDelimiter = ' '-- Thousand Delimiter
   END

   -- Assign decimal delimiter format, 
   -- at this point, the internal delimiter is always '.'
   SET @strResult = REPLACE(@strResult, '.', @strDecDelimiter)

   -- Init the position of a possible decimal delimiter
   SET @tintDecPos = CHARINDEX(@strDecDelimiter, @strResult)

   -- Init the decimal and the main value
   IF @tintDecPos > 0 BEGIN
      -- Current value contains a decimal delimiter
      SET @strIntValue = SUBSTRING(@strResult, 1, @tintDecPos - 1)
      SET @strDecValue = SUBSTRING(@strResult, @tintDecPos + 1, LEN(@strResult)) 
   END
   ELSE BEGIN
      -- Current value contains no decimal delimiter
      SET @strIntValue = @strResult
      SET @strDecValue = REPLICATE('0', @tintNoDecimals)
   END

   -- String Handling
   SET @strTmp = ISNULL(LTRIM(RTRIM(@strIntValue)), '')
   SET @strIntValue = ''

   -- Init number of required thousand delimiter
   IF LEN(@strTmp) > 3 
      SET @tintNoOfThDel = (LEN(@strTmp) / 3)

   -- String conversion
   WHILE (@tintWhile < @tintNoOfThDel)
   BEGIN
  
      SET @tintWhile = @tintWhile + 1
  
      IF LEN(@strTmp) > 3
         SET @strIntValue = @strThDelimiter + RIGHT(@strTmp, 3) + @strIntValue
      ELSE
         SET @strIntValue = @strTmp + @strIntValue  
  
      SET @strTmp = LEFT(@strTmp, LEN(@strTmp) - 3)

   END

   -- Init final string
   SET @strIntValue = LEFT(@strTmp, LEN(@strTmp)) + @strIntValue

   -- Init decimal part
   IF @tintNoDecimals > 0
      SET @strResult = @strIntValue + @strDecDelimiter + @strDecValue
   ELSE
      SET @strResult = @strIntValue

   -- Handle negative values
   IF @blnIsMinus = 1 
      SET @strResult = '-' + @strResult

   -- Return
   RETURN (@strResult)


END



GO