June 25, 2003 at 8:42 am
Before I "re-invent the wheel," I thought I would ask... Does anyone have any code that will right justify and format numeric data with commas?
June 25, 2003 at 1:21 pm
Here is a copy of the wheel that I re-invented, but I'm not particularly happy with the code. Anyone have any better ideas?
Thanks,
Jon
create function DBA_FormatNumericData
(
@float float, -- An integer value that will be formatted
@DesiredWidth smallint, -- The desired width of the output string
@DecimalCount smallint -- Number of decimal places to display
)
returns varchar(30)
as
begin
/****
* Define work variables
****/
declare @floatString varchar(30) -- A string representation of @int
,@pos smallint -- The character position within a string
,@long smallint -- The length of a string
,@delta smallint -- The difference between @long and @pos
set @floatString = convert(varchar(20), @float) -- Convert float to a string
if @DesiredWidth > 30 -- Fix a potential width problem
set @DesiredWidth = 30
/****
* Add comma(s) and adjust decimal positions
****/
set @long = len(@floatString) -- Length of string, at this point
set @pos = charindex('.', @floatString) -- Check for a decimal in the string
if @pos = 0 and @DecimalCount > 0 -- Pad with zeroes
begin
set @floatString = @floatString + '.' + replicate ('0', @DecimalCount) -- Pad with zeroes
-- Recalculate @long and @pos
set @long = len(@floatString) -- Length of string, at this point
set @pos = charindex('.', @floatString) -- Check for a decimal in the string
end
if @pos > 0
begin
-- Decimal found in string
-- Adjust the number of decimal places
set @delta = @long - @pos - @DecimalCount
if @delta < 0
set @floatString = @floatString + replicate ('0', @delta * -1) -- Pad with zeroes
else
if @DecimalCount = 0
set @floatString = substring(@floatString, 1, @pos - 1) -- Trim decimal (.)
else
set @floatString = substring(@floatString, 1, @pos + @DecimalCount) -- Trim decimal places
-- Add comma(s)
while @pos > 4
begin
set @pos = @pos - 3
set @floatString = substring(@floatString, 1, @pos - 1) + ',' + substring(@floatString, @pos, len(@floatString))
end
end
else
begin
-- Decimal NOT found in string
-- Add comma(s)
set @pos = len(@floatString)
while @pos > 3
begin
set @pos = @pos - 3
set @floatString = substring(@floatString, 1, @pos) + ',' + substring(@floatString, @pos + 1, len(@floatString))
end
end
/****
* Right justify the string, if necessary
****/
set @long = len(@floatString)
if @long > @DesiredWidth
set @DesiredWidth = len(@floatString)
else
set @floatString = space(@DesiredWidth - @long) + @floatString
/****
* Return
****/
return (substring(@floatString, 1, @DesiredWidth))
end
go
select test = ProdDBAWeb.dbo.DBA_FormatNumericData (123, 20, 0)
select ProdDBAWeb.dbo.DBA_FormatNumericData (12.3, 20, 2)
June 25, 2003 at 1:50 pm
Yes, you'd think there'd be a simple function, but I didn't see one either.
However, you could use a DTS transformation task such as this:
'********************************************
' Visual Basic Transformation Script
'********************************************
Function Main()
If DTSSource("column name")<>"" THEN
' add spaces in front of number to fill field, where field is char(20):
DTSDestination("column name") =space(20-len(FormatNumber(DTSSource _ ("column name")))) & FormatNumber(DTSSource("column name"))
End If
Main = DTSTransformStat_OK
End Function
... but I guess I invented a wheel.
Bill
June 26, 2003 at 7:25 am
Samples
DECLARE @val money
SET @val = 1234567.89
SELECT CONVERT(char(15),@val,1)
DECLARE @val int
SET @val = 1234567
SELECT LEFT(CONVERT(char(18),CAST(@val AS money),1),15)
DECLARE @val decimal(9,2)
SET @val = 1234567.89
SELECT CONVERT(char(15),CAST(@val AS money),1)
DECLARE @val real
SET @val = 123456.78
SELECT CONVERT(char(15),CAST(@val AS money),1)
Edited by - davidburrows on 06/26/2003 07:25:17 AM
Far away is close at hand in the images of elsewhere.
Anon.
June 26, 2003 at 12:23 pm
DavidBurrows,
What would you do if you need more then 2 decimal places.
A simple way to do the right justification would be to concatenate the SPACE and CONVERT(varchar, ). For example, you had a column that was 8 characters wide: SPACE(8 - LEN(@variable)) + CONVERT(varchar(8), @variable)
I do not know of a simple way to put the commas in without looping through the number finding the decimal place and working backwards from there.
June 26, 2003 at 6:11 pm
Something like this developed into a function might do the trick.
Ex.
DECLARE @var varchar(40)
declare @tempVar varchar(40)
set @var = '48501239955.486'
set @tempVar = rtrim(ltrim(reverse(@var)))
select @var = reverse(left(@tempVar,charindex('.',@tempVar))), @tempVar = right(@tempVar,len(@tempVar) - charindex('.',@tempVar))
while len(@tempVar) > 3
begin
select @var = ',' + reverse(left(@tempVar,3)) + @var, @tempVar = right(@tempVar,len(@tempVar) - 3)
end
select @var = reverse(@tempVar) + @var
print @var
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply