SQL Server 2000: Formatting output

AucklandAsText = crystalcommon.dbo.FormatIntegerString(CONVERT(decimal(24,3), AucklandQty))

AucklandAsText = crystalcommon.dbo.FormatIntegerString(CONVERT(INTEGER, AucklandQty))

AucklandAsText = ''$ '' + crystalcommon.dbo.FormatIntegerString(CONVERT(INTEGER, AucklandValue, 1))

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FormatIntegerString] ( @Input varchar( 6000 ) )
RETURNS varchar( 8000 )
AS

BEGIN

DECLARE @TempData varchar( 8000 ),
@Output varchar( 8000 ),
@DecimalPortion varchar( 2000 ),
@Length int,
@HasDecimal int,
@Done bit,
@HasNegative int

-- Default some of our variables
SELECT @Output = '',
@Done = 0

-- Determine if we have a negative value (sign = '-')
SELECT @HasNegative = PATINDEX( '-%', @Input )

IF ( @HasNegative > 0 )
BEGIN
-- If we do, remove the first character, must be done before we look for decimal points etc
SELECT @Input = RIGHT(@Input,LEN(@Input)-1)
END

-- Determine if we have a decimal point in the input string
SELECT @HasDecimal = PATINDEX( '%.%', @Input )

IF ( @HasDecimal > 0 )
BEGIN
-- If we do, store the relevant characters to the right of the decimal place
SELECT @TempData = RTRIM( SUBSTRING( @Input, 1, ( PATINDEX( '%.%', @Input ) - 1 ) ) )
SELECT @DecimalPortion = RTRIM( SUBSTRING( @Input, @HasDecimal, DATALENGTH( @Input ) - ( @HasDecimal - 1 ) ) )
END
ELSE
BEGIN
-- No decimal place, grab the whole string
SELECT @TempData = RTRIM( @Input )
SELECT @DecimalPortion = ''
END

SELECT @Length = DATALENGTH( RTRIM( @TempData ) )

-- If we have 3 or less numbers, return the string as-is
IF ( @Length <= 3 OR @Length IS NULL )
BEGIN
SELECT @Output = RTRIM( @TempData )
SELECT @Done = 1
END

-- Loop through the string, from back to front, adding , as needed,
-- until we have 3 or fewer characters left at the front of the string
WHILE ( @Done = 0 )
BEGIN
SELECT @Output = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @Output )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @Output = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @Output )
SELECT @Done = 1
END
END

-- Add back the decimal portion, if any
SELECT @Output = RTRIM( @Output )
+ RTRIM( @DecimalPortion )

-- Add back the negative sign if any
IF ( @HasNegative > 0 )
BEGIN
SELECT @Output = '-' + @Output
END

-- Return the new "formatted" result
RETURN( @Output )

END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FormatMoneyString] ( @Input varchar( 6000 ) )
RETURNS varchar( 8000 )
AS

BEGIN

DECLARE @TempData varchar( 8000 ),
@Output varchar( 8000 ),
@DecimalPortion varchar( 2000 ),
@Length int,
@HasDecimal int,
@Done bit,
@HasNegative int

-- Default some of our variables
SELECT @Output = '',
@Done = 0

-- Determine if we have a negative value (sign = '-')
SELECT @HasNegative = PATINDEX( '-%', @Input )

IF ( @HasNegative > 0 )
BEGIN
-- If we do, remove the first character, must be done before we look for decimal points etc
SELECT @Input = RIGHT(@Input,LEN(@Input)-1)
END

-- Determine if we have a decimal point in the input string
SELECT @HasDecimal = PATINDEX( '%.%', @Input )

IF ( @HasDecimal > 0 )
BEGIN
-- If we do, store the relevant characters to the right of the decimal place
SELECT @TempData = RTRIM( SUBSTRING( @Input, 1, ( PATINDEX( '%.%', @Input ) - 1 ) ) )
SELECT @DecimalPortion = RTRIM( SUBSTRING( @Input, @HasDecimal, DATALENGTH( @Input ) - ( @HasDecimal - 1 ) ) )
END
ELSE
BEGIN
-- No decimal place, grab the whole string
SELECT @TempData = RTRIM( @Input )
SELECT @DecimalPortion = ''
END

SELECT @Length = DATALENGTH( RTRIM( @TempData ) )

-- If we have 3 or less numbers, return the string as-is
IF ( @Length <= 3 OR @Length IS NULL )
BEGIN
SELECT @Output = RTRIM( @TempData )
SELECT @Done = 1
END

-- Loop through the string, from back to front, adding , as needed,
-- until we have 3 or fewer characters left at the front of the string
WHILE ( @Done = 0 )
BEGIN
SELECT @Output = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @Output )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @Output = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @Output )
SELECT @Done = 1
END
END

-- Add back the decimal portion, if any
SELECT @Output = RTRIM( @Output )
+ RTRIM( @DecimalPortion )

-- Add $ Sign
SELECT @Output = '$ ' + @Output

-- Add back the negative sign if any
IF ( @HasNegative > 0 )
BEGIN
SELECT @Output = '- ' + @Output
END

-- Return the new "formatted" result
RETURN( @Output )

END

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FormatNumberNoTrailingZerosAfterDecPoint] ( @Input varchar( 6000 ) )
RETURNS varchar( 8000 )
AS

BEGIN
DECLARE @TempData varchar( 8000 ),
@Output varchar( 8000 ),
@DecimalPortion varchar( 2000 ),
@Length int,
@HasDecimal int,
@Done bit,
@HasNegative int

-- Default some of our variables
SELECT @Output = '',
@Done = 0

-- Determine if we have a negative value (sign = '-')
SELECT @HasNegative = PATINDEX( '-%', @Input )

IF ( @HasNegative > 0 )
BEGIN
-- If we do, remove the first character, must be done before we look for decimal points etc
SELECT @Input = RIGHT(@Input,LEN(@Input)-1)
END

-- Determine if we have a decimal point in the input string
SELECT @HasDecimal = PATINDEX( '%.%', @Input )

IF ( @HasDecimal > 0 )
BEGIN
-- If we do, store the relevant characters to the right of the decimal place
SELECT @TempData = RTRIM( SUBSTRING( @Input, 1, ( PATINDEX( '%.%', @Input ) - 1 ) ) )
SELECT @DecimalPortion = RTRIM( SUBSTRING( @Input, @HasDecimal, DATALENGTH( @Input ) - ( @HasDecimal - 1 ) ) )
END
ELSE
BEGIN
-- No decimal place, grab the whole string
SELECT @TempData = RTRIM( @Input )
SELECT @DecimalPortion = ''
END

SELECT @Length = DATALENGTH( RTRIM( @TempData ) )

-- If we have 3 or less numbers, return the string as-is
IF ( @Length <= 3 OR @Length IS NULL )
BEGIN
SELECT @Output = RTRIM( @TempData )
SELECT @Done = 1
END

-- Loop through the string, from back to front, adding , as needed,
-- until we have 3 or fewer characters left at the front of the string
WHILE ( @Done = 0 )
BEGIN
SELECT @Output = ','
+ RTRIM( SUBSTRING( @TempData, @Length - 2, 3 ) )
+ RTRIM( @Output )
SELECT @TempData = RTRIM( SUBSTRING( @TempData, 1, @Length - 3 ) )
SELECT @Length = DATALENGTH( RTRIM( @TempData ) )
IF ( @Length <= 3 )
BEGIN
SELECT @Output = RTRIM( SUBSTRING( @TempData, 1, @Length ) )
+ RTRIM( @Output )
SELECT @Done = 1
END
END

-- Add back the decimal portion, if any
SELECT @Output = RTRIM( @Output )
+ RTRIM( @DecimalPortion )

-- Remove trailing zeros after the decimal point
SELECT @Output =
CASE WHEN PATINDEX('%[1-9]%', REVERSE(@Output)) < PATINDEX('%.%', REVERSE(@Output)) THEN LEFT(@Output, LEN(@Output) - PATINDEX('%[1-9]%', REVERSE(@Output)) + 1) ELSE LEFT(@Output, LEN(@Output) - PATINDEX('%.%', REVERSE(@Output))) END

-- Add back the negative sign if any
IF ( @HasNegative > 0 )
BEGIN
SELECT @Output = '-' + @Output
END

-- Return the new "formatted" result

Return @Output

END