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