How to achieve default value if column value is NULL?

Viewed 7131

I want to retrieve some column values from table with these conditions.

  • If value is NULL (or) Empty String , return some user defined value
  • If not above condition , return it's value.

How can I figure it out ?

Here is my Table query..

CREATE TABLE AUCTION_CAR_BID(
bid_seq bigint NOT NULL AUTO_INCREMENT,
auction_car_seq bigint NOT NULL,
bid_group_seq bigint NOT NULL,
bid_price int DEFAULT 0 NOT NULL,
over_bid_price int DEFAULT -1 NOT NULL,
result_id int DEFAULT 0 NOT NULL,
remark varchar(500),
PRIMARY KEY (bid_seq)) 
ENGINE = InnoDB DEFAULT CHARACTER SET utf8;

Here is my efforted codes to get it..

SELECT
    COALESCE(OVER_BID_PRICE, -1)
FROM
    AUCTION_CAR_BID
WHERE
    BID_SEQ = 2354435345;

Another :

SELECT
    CASE
        WHEN OVER_BID_PRICE IS NULL
        OR TRIM(OVER_BID_PRICE) = '' THEN -1
        ELSE OVER_BID_PRICE
    END OVER_BID_PRICE
FROM
    AUCTION_CAR_BID
WHERE
    BID_SEQ = 2354435345;

But I always get empty String value(not -1) if given id is not in my table.

Any suggestions would be really appreciated !

4 Answers
Related