?? statement in sql

Question

I have the following problem:
We have this request:

select price*hours as payment from table employees

Now, if the result of this multiplication is 0, I would like the payment to be "x" and not 0.
Translated to nonsql, this would mean:

(price*hours) != 0 ? (price*hours) : "x"

Any ideas how I can implement this sql command?
Thank!

Answer 1

SELECT COALESCE(CAST(NULLIF(price * hours, 0) AS VARCHAR), 'x') AS payment_text
  FROM employees;

... but I agree with @Marc Gravell that such formatting should be done in the "front end".

Answer 2

Well, ??applicable to NULL- in this case COALESCEor ISNULL- but you seem to mean it 0- and in this case it’s simple:

SELECT ...blah...,
       CASE price*hours WHEN 0 THEN 'x' ELSE price*hours END AS [payment]
...more blah...

, . , 0, ( ..).

Answer 3

To solve such problems you can use statemnt CASE

SELECT  payment = CASE
        WHEN price * hours = 0 THEN 'X'
        ELSE price * hours
        END

Answer 4

select payment =
  case price*hours
    when 0 THEN 'x'
    else price*hours
  end
from table employees

Answer 5

CASE
  WHEN somevar = 0 then "x"
  ELSE somevar
  END

Answer 6

Well, you have to convert the resulting number to a string or it won’t work, since “x” is not a number.

Maybe something like this:

Select Case 
       When Price * Hours = 0 Then "x" 
       Else Convert(varchar(50), (Price * Hours)) End as "Price"

From dbo.Table