CODEWITHSUNDEEP
sql-server
Freonthewhite
Freonthewhite

Reputation: 29

SQL convert numeric 0 to text 000 in a case when statement

I'm in management studio trying to write a query on a numeric field using a case when that looks like this:

select
case
when table1.copay*100 = 0 then '000' else table1.copay*100 end as copay
from table 1

I've tried all sorts of casting, padding, convert to get it to display 000 when table1.copay*100 = 0 but no matter what I try it brings back just 0. I know that's because it's a number, but I've tried converting to a string with no success.

Any thoughts?

Upvotes: 0

Views: 537

Answers (1)

Freonthewhite
Freonthewhite

Reputation: 29

John Cappelletti's comment answered the question:

One option is format(copay*100 ,'000') this will return a string. HOWEVER, it should be used sparingly due to performance issues.

Upvotes: 1

Related Questions