?? operator in sql

I have the following issue :

We have this query :

select price*hours as payment from table employees

Now, i开发者_C百科f the result of that multiplication it's 0, I'd like payment to be "x", not 0.

Translated in nonsql this would mean :

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

Any ideas how could i implement this sql command ?

Thanks!

Well, ?? would apply to NULL - in which case COALESCE or ISNULL - but you seem to mean 0 - in which case just:

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

However, I would advise doing this as a UI concern rather than in the database. You may also want to think about how close to 0 it needs to be (floating point rounding etc).

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

...but I agree with @Marc Gravell that this kind of formatting should be done in the 'front end'.

To tackle such problems, you can use the CASE statemnt

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

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

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

Well you would have to convert the resulting number in to a string or it will fail as "x" isnt 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