May 20, 2003 at 3:05 am
Have a varchar column with numeric values which equate to dollars or pounds sterling etc. because of the size of the transaction, need to format the information such that
initial value result
123 123
12300 12.3K
12300000 12.3M
Note, there are no commas in the data
Any ideas ?
May 20, 2003 at 3:37 am
This might do the trick.
create function dbo.fn_format (@string as varchar(25))
returns varchar(25) as
begin
if @string > 1000000 set @string = left(@string,len(@string)-6) + '.' + substring(@string,len(@string)-5,1) + 'M'
else if @string > 1000 set @string = left(@string,len(@string)-3) + '.' + substring(@string,len(@string)-2,1) + 'K'
return(@string)
end
go
select dbo.fn_format('123'), dbo.fn_format('1230'), dbo.fn_format('12300'), dbo.fn_format('123000'), dbo.fn_format('1230000'), dbo.fn_format('12300000')
drop function dbo.fn_format
I think you also posted a query about removing trailing zeros which you might want to reference if you want to avoid results like 123.0K
You might need to tweak this a bit depending on how many decimal places you want. If you want to round numbers, then you might want to divide the input string by 1 million or 1 thousand and then round the result.
HTH
Jeremy
Edited by - Jeremy Kemp on 05/20/2003 03:42:25 AM
May 20, 2003 at 4:44 am
Or something like this where you can also control rounding.
create function dbo.fn_format (@value numeric(38,1))
returns varchar(200) as
BEGIN
declare @string as varchar(200)
if @value >= 1000000
set @string = replace(rtrim(replace(cast((@value / 1000000) as varchar(200)),'0',' ')), ' ', '0') + 'M'
else if @value >= 1000
set @string = replace(rtrim(replace(cast((@value / 1000) as varchar(200)),'0',' ')), ' ', '0') + + 'K'
else
set @string = cast(cast(@value as int) as varchar(200))
return(@string)
END
May 20, 2003 at 6:32 am
How About this:
Create Table #Sales( Value Varchar(50) )
Insert into #Sales Select 123
Insert into #Sales Select 1230
Insert into #Sales Select 12300
Insert into #Sales Select 123000
Insert into #Sales Select 1230000
Insert into #Sales Select 12300000
Insert into #Sales Select 123000000
Insert into #Sales Select 1230000000
Insert into #Sales Select 12300000000
Begin
Select Value
,NewValue =
Convert( Varchar(50), Round( Value / Convert( numeric(38, 0 ), '1' + Replicate( '0', 3* Floor( ( Len( RTrim( Value ) ) - 1) / 3.0 ) ) ), 3) )+
Case (Len( RTrim( Value ) )- 1 ) / 3
When 4 Then 'T'
When 3 Then 'G'
When 2 Then 'M'
When 1 Then 'K'
When 0 Then ''
Else ' Real Big Ones'
End
From #Sales
End
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply