CODEWITHSUNDEEP
sql
Sergio Ramos
Sergio Ramos

Reputation: 123

Replace empty value in SQL query

I've got the next query

Select LAST_NAME from test.[USER]
        Where ID = (Select test_support from test.UPGRADE
        Where KEYED_NAME Like '%abc%'
        and STATE = 'Active')

The result like:

 LastName1

But if the Keyed_Name doesn't exist, the result is empty. I get the empty column:

enter image description here

How to change it to receive some other value like '-' instead empty.

So if the query result is empty, I will receive:

enter image description here

I tried next query

DECLARE @EmptyString NVARCHAR( 10 ) = ''; 
        Select CASE WHEN LAST_NAME <> @EmptyString THEN LAST_NAME ELSE '-' END
        from test.[USER]
        Where ID = (Select test_support from test.UPGRADE
        Where KEYED_NAME Like '%abc%'
        and STATE = 'Active')

but it works only for case when the string is not empty.

Upvotes: 0

Views: 66

Answers (2)

Jonas Metzler
Jonas Metzler

Reputation: 5975

We can put the whole query in COALESCE and take '-' if the query doesn't find records:

SELECT COALESCE(
(SELECT LAST_NAME from users
        Where ID = (SELECT test_support FROM upgrade
        Where KEYED_NAME LIKE '%abc%'
        AND state = 'Active')), '-') AS LAST_NAME;

Try out here: db<>fiddle

Upvotes: 1

HSS
HSS

Reputation: 176

This should do it:

select top 1 * from
(Select LAST_NAME from test.[USER]
        Where ID = (Select test_support from test.UPGRADE
        Where KEYED_NAME Like '%abc%'
        and STATE = 'Active')
union
Select NULL LAST_NAME) t

Upvotes: 0

Related Questions