I am moving data from Oracle to an MS SQL server. I'm using a TO_CHAR Format module for the amount field to change the amount to the desired format using FM. I'm looking for an equivalent function in the SQL server to get the same output.

Oracle:

Select Amount, TO_CHAR(Amount, 'FM099999999V99MI') as Converted from Billing_table

Output:

CodePudding user response：

SQL Server does have format(), but be warned. It should be used sparingly due to performance issues.

Example

Declare @YourTable table (Amount*100 decimal(15,2))
Insert Into @YourTable values
 (0)
,(1985.56)
,(18.63)
,(-258.93)
,(-6.02)


Select Amount 
      ,Converted =  format(Amount*100,'00000000000;00000000000-')
 From  @YourTable

Results

Amount  Converted
0.00    00000000000
1985.56 00000198556
18.63   00000001863
-258.93 00000025893-
-6.02   00000000602-

CodePudding user response：

There are probably 100 different ways to do this. FORMAT() definitely seems cleaner and more intuitive but like John I stay away from it due to performance overhead.

CREATE TABLE dbo.nums(val decimal(15,2));

INSERT dbo.nums(val) VALUES(0),
(1985.56),
(18.63),
(-258.93),
(-6.02);

SELECT val, RIGHT(REPLICATE('0',11) 
      RTRIM(CONVERT(int,100*ABS(val))),11) 
      CASE WHEN val < 0 THEN '-' ELSE '' END
  FROM dbo.nums;

Results:

• Example db<>fiddle

CodePudding user response：

According to this answer and custom format string you may use:

with a as (
  select *
  from(values (0), (1985.56), (18.63), (-258.93), (-6.0234), (-10)) as t(val)
)
select
  val,
  /*Set explicit format with fixed decimal points*/
  replace(format(val, '000000000.00;000000000.00-'), '.', '') as formatted,
  /*Explicit multiplication*/
  format(val*100, '00000000000;00000000000-') as formatted2
from a
GO

      val | formatted    | formatted2  
--------: | :----------- | :-----------
   0.0000 | 00000000000  | 00000000000 
1985.5600 | 00000198556  | 00000198556 
  18.6300 | 00000001863  | 00000001863 
-258.9300 | 00000025893- | 00000025893-
  -6.0234 | 00000000602- | 00000000602-
 -10.0000 | 00000001000- | 00000001000-

db<>fiddle here