Replace empty value in SQL query

Question

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:

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

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

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.

So, the query gives you all matching last names (which can be zero, one or many rows), but in case there is no match, you want a dummy result row with '-'? — Thorsten Kettner
– Thorsten Kettner, Commented Dec 14, 2022 at 10:25
What is your DBMS? Please always tag your SQL requests with your DBMS. We may need to know this for our answers. — Thorsten Kettner
– Thorsten Kettner, Commented Dec 14, 2022 at 10:27

Jonas Metzler · Accepted Answer · 2022-12-14 10:27:38Z

1

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

edited Dec 14, 2022 at 10:27

answered Dec 14, 2022 at 10:18

Jonas Metzler

6,8363 gold badges9 silver badges24 bronze badges

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

3 Comments

Thorsten Kettner Over a year ago

This will crash when there is more then one user Where KEYED_NAME LIKE '%abc%'.

Jonas Metzler Over a year ago

@ThorstenKettner True. As I understood the question, the query will always fetch one entry only, so this would not happen. But the requirement is not 100% clear, we could also understand it in another way. So I think - if this is a possible risk in OP's situation - they should edit the question and explain what to do in this case (if it's enough to fetch one of these rows or if all should be selected). In my opinion, in this case doing this in the application or in a function should be prefered rather than in a query. Still using a query for that will likely lead to very poor readability.

Sergio Ramos Over a year ago

@JonasMetzler Thanks! This will work because the only 1 user could be in the result, in my case state active makes it

HSS · Accepted Answer · 2022-12-14 10:18:31Z

0

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

answered Dec 14, 2022 at 10:18

HSS

1764 bronze badges

2 Comments

Jonas Metzler Over a year ago

Please note: "Top" is DBMS-specific and the OP didn't tag theirs, "union" is slower than "union all".

Thorsten Kettner Over a year ago

Without an ORDER BY clause the DBMS is free to pick any rows with TOP, LIMIT, FETCH FIRST ROW ONLY or whatever clause the DBMS features. So, even in case there is a match, you may still see NULL as the result. And yes, UNION [DISTINCT] makes no sense, because there are no duplicates to remove. Use UNION ALL instead. And yes again, TOP and the brackets ([]) are not standard SQL and only work in few DBMS.

Collectives™ on Stack Overflow

Replace empty value in SQL query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related