CODEWITHSUNDEEP
sqlsql-serverms-accessformat
Taryn
Taryn

Reputation: 247650

SQL Server Format() function

I have a current query in an MS Access database that uses the Format() function to convert a number (long integer) to a string.

The table has been redesigned and moved to SQL server. The field I need to convert is not a money field that I need to drop everything after the period and convert it to a string with a certain number of characters.

Sample Data:

Amount
228.00000
1896.00000
6411.00000
317.00000
4830.00000
898.00000
1778.00000
1308.00000
45139.00000
424.00000

Current MS Access query:

SELECT Format(CallAmt],"000000000000000") AS Amount
FROM Fed

New query:

SELECT Convert(varchar(15), FedAmount)
FROM Fed.SM_T_Call

I would like my final Product to look like this:

Amount
000000000000228
000000000001896
000000000006411
000000000000317
000000000004830
000000000000898
000000000001778
000000000001308
000000000045139
000000000000424

I don't know how to drop everything after the period and add the extra zeros to my result. Can anyone offer any assistance?

Upvotes: 1

Views: 12755

Answers (5)

onedaywhen
onedaywhen

Reputation: 57023

@gbn'd answer is a good one for SQL Server and uses proprietary functions RIGHT() and REPLICATE().

Noting that you've already had to port this once, the following may be a more portable version because it uses Standard SQL functions. Note the string concatenation operator in Standard SQL is ||, whereas SQL Server uses + for the same (and MS still claims it is compliant, go figure). However, the proposed code still scores highly on the portability scale because transforming `|| to + is trivial:

REVERSE(CAST(REVERSE(CAST(CAST(Amount AS INTEGER) AS VARCHAR(15))) 
   + '000000000000000' AS CHAR(15)))

Upvotes: 0

gbn
gbn

Reputation: 432200

You can use:

  • CAST to decimal to have a fixed number of decimal places
  • Use STR to right justify
  • Use RIGHT('000000000000000' + CAST(MyNumber AS varchar(15)), 15) to have leading zeros

Or all of the above: but we don't know what output you want...

Edit, after update

RIGHT('000000000000000' + CAST(CAST(Amount AS int) AS varchar(15)), 15)

Edit, October 2011

SQL Server 2012 (Denali) will have Format function.

Edit, after comment by onedaywhen

DECLARE @len tinyint;
SET @len = 15;
RIGHT(REPLICATE('0', @len) + CAST(CAST(Amount AS int) AS varchar(255)), @len)

Upvotes: 6

John Sobolewski
John Sobolewski

Reputation: 4572

select Cast(Cast(fedamount as int), as varchar(15))
from Fed.SM_T_Call

Might actually work?

Upvotes: 0

Chains
Chains

Reputation: 13157

You can use the decimal data type:

declare @var decimal(15,6)
set @var = 228564.12345678

And then if you want it to be a varchar after that:

select convert(varchar(15),@var)

Or in a select, it could look like this:

select 
   [field1] as OldValue,
   convert(decimal(15,6),[field1]) as NewValue
from 
   myTable

Upvotes: 0

David
David

Reputation: 1611

SELECT CAST(CAST(FLOOR(FedAmont) AS INT) AS VARCHAR(40)) + '.00'

Upvotes: 0

Related Questions