-----Originalnachricht-----
Hallo,
wie formatiere ich in T-SQL die Ausgabe von Zahlen in
ein
deutsches Format, also z.B. "1.234,55" mit tausender
Trenner und Komma als Dezimal ?!
Danke
Maik
.
Hi Maik,
normalerweise ist das Sache des Clients bzw. der
Präsentationsschicht. Ich habe jedoch vor kurzem mal
eine "User Defined Function" dafür geschrieben, die dir
ggf. weiterhilft. Das ganze funktioniert dann auch mit
anderen Formaten (engl., spanisch, etc.).
Aufruf Beispiele:
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
Hier die Funktion...
-------------------------
CREATE FUNCTION dbo.fn_ConvertNumber (@dblValue FLOAT,
@intLCID INT,
@tintNoDecimals
TINYINT)
RETURNS VARCHAR(50)
/*
TSM, 04/06/2004, 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
-----------------
Gruß
Thomas
http://www.sqlscripter.com