SQL Server Format() function

Question

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?

What datatype is FedAmount in SQL Server? You said its not a money field, what is it in SQL Server after it was moved — Ta01
– Ta01, Commented Aug 16, 2011 at 17:49
It would help if you edit your question and show how you want the final output to look. — Michael Riley - AKA Gunny
– Michael Riley - AKA Gunny, Commented Aug 16, 2011 at 18:03
Use my RIGHT solution then to have leading spaces with a CAST to int — gbn
– gbn, Commented Aug 16, 2011 at 18:51

onedaywhen · Accepted Answer · 2011-10-24 07:07:13Z

6

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)

edited Oct 24, 2011 at 7:07

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

answered Aug 16, 2011 at 18:18

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Taryn Over a year ago

thank you so much. I had no clue how I was going to figure this out. Sorry again for not including sample data.

onedaywhen Over a year ago

Using REPLICATE('0', 15) would make your code easier to read.

gbn Over a year ago

@onedaywhen: True. In real life I'd probably wrap it in a UDF and have length as a parameter...

David · Accepted Answer · 2011-08-16 18:01:14Z

0

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

answered Aug 16, 2011 at 18:01

David

1,6111 gold badge11 silver badges22 bronze badges

Comments

Chains · Accepted Answer · 2011-08-16 18:07:09Z

0

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

answered Aug 16, 2011 at 18:07

Chains

13.2k8 gold badges48 silver badges62 bronze badges

Comments

Adriano Carneiro · Accepted Answer · 2011-08-17 15:34:58Z

0

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

Might actually work?

edited Aug 17, 2011 at 15:34

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

answered Aug 16, 2011 at 17:50

John Sobolewski

4,5621 gold badge23 silver badges27 bronze badges

Comments

onedaywhen · Accepted Answer · 2011-10-24 07:27:05Z

0

@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)))

answered Oct 24, 2011 at 7:27

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

SQL Server Format() function

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related