SQL Server Query for checking for empty string and replacing with something else

Question

I have an integration type project where my select column often has this code:

select 
CASE MyColumn
    WHEN '' THEN '&' 
    ELSE MyColumn
END

Is there a simpler approach? Sort of like coalesce with nulls? I'm finding the query really hard to read, etc.

Thanks

see 'accepted' answer below. I actually didn't need to preserve the null, and the main goal afterall, was to simplify the query so it would be easier to read without repeating 'MyColumn' so many times. — Raymond
– Raymond, Commented Nov 16, 2013 at 0:26

M.Ali · Accepted Answer · 2013-11-15 21:58:25Z

2

I guess you can try something like this...

 COALESCE(NULLIF(MyColumn,''), '&')

answered Nov 15, 2013 at 21:58

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

4 Comments

Michael Fredrickson Over a year ago

This isn't functionally equivalent... the original implementation would preserve a NULL value, this replaces NULL with '&'.

M.Ali Over a year ago

well spotted, ops @Vishal and me exactly the same answer :)

M.Ali Over a year ago

I guess in this case, CASE statement should do the job.

Raymond Over a year ago

Thanks - I actually didn't need to preserve the NULL, but if I did, one of the other answers would do the trick... though remember, the initial goal was to simplify the select compared to a case when structure, and I'm not sure if any of the other answers simplifies it. This one does.

Community · Accepted Answer · 2017-05-23 10:25:38Z

1

You could use COALESCE, following would check null and empty string from here -

COALESCE(NULLIF(MyColumn,''), '&')

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Nov 15, 2013 at 21:58

Vishal

12.4k20 gold badges85 silver badges129 bronze badges

2 Comments

Michael Fredrickson Over a year ago

This isn't functionally equivalent... the original implementation would preserve a NULL value, this replaces NULL with '&'.

Vishal Over a year ago

That's true, don't see a way around the case then.

conan · Accepted Answer · 2013-11-15 22:11:14Z

0

Following on from @Michael Fredrickson's comment

NULLIF(COALESCE(NULLIF(COALESCE(MyColumn,'###'),''), '&'),'###')

Replace ### with anyvalue you know won't appear in the field. Not sure it's easier to read than the case statement though.

answered Nov 15, 2013 at 22:11

conan

1245 bronze badges

Comments

d89761 · Accepted Answer · 2013-11-15 23:33:39Z

0

This should work, and preserve nulls:

SELECT COALESCE(NULLIF(MyColumn, ''), NULLIF(MyColumn + '&', ''), MyColumn)

answered Nov 15, 2013 at 23:33

d89761

1,4449 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Server Query for checking for empty string and replacing with something else

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related