SQL Server replacing null with empty string

Question

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

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Aug 20, 2016 at 9:08

Tim Biegeleisen · Accepted Answer · 2016-08-20 08:52:42Z

1

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

edited Aug 20, 2016 at 8:52

answered Aug 20, 2016 at 8:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheGameiswar · Accepted Answer · 2016-08-20 08:44:13Z

0

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..

answered Aug 20, 2016 at 8:44

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

SQL Server replacing null with empty string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related