CODEWITHSUNDEEP
sql-server
Lady_A
Lady_A

Reputation: 27

SQL Server replacing null with empty string

I need to know what SQL statement do I use to produce an output where if a cell contains null then it pulls the information from the column next to it.

To explain better: I have three tables, table 1 contains 7 columns, table 2 contains 6 columns, table 3 contains 4 columns.

I need select 1 column from table 1 followed by a dash then select 3 columns from another table followed by a dash and then another column from a different table,but where ever a column contains null, the values must be replaced by a dash '-'.

This is the code I have so far :

SELECT 
    Episode_Name, '-', Pnr_FName, Pnr_LName, Guest, '-', Car_Make
FROM 
    EPISODES, PRESENTERS, CARS

I did try:

(CASE WHEN Guest = null THEN ''
END EPISODE)

and I bound it in the select statement but it did not work.

Any help would be appreciated! thanks

Upvotes: 1

Views: 906

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You can't check a column value for NULL using the = operator, because the result will also be NULL. Instead, you should use IS NULL and IS NOT NULL. But for readability purposes, I would use COALESCE instead of CASE statements.

Use COALESCE:

SELECT CONCAT(COALESCE(Episode_Name, ' '),
              '-',
              COALESCE(Pnr_FName, ' '),
              COALESCE(Pnr_LName, ' '),
              COALESCE(Guest, ' '),
              '-',
              COALESCE(Car_Make, ' '))
FROM EPISODES, PRESENTERS, CARS

By the way, you should use explicit join syntax instead of using commas in the FROM clause, e.g.

FROM EPISODES e INNER JOIN PRESENTERS p
    ON e.col = p.col

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

I did try : (CASE WHEN Guest = null THEN '' END EPISODE)

try

case when guest is null then '' end

You can't compare nulls,to check if nulls are present ,you have to use IS NULL..

Upvotes: 0

Related Questions