Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

Check for empty values and return something else (i.e. like ISNULL or COALESCE does for null values)

I have empty fields in my table and i need to check for this and return another value. ISNULL and COALESCE checks for nulls only. I found this:

SELECT * FROM tbl WHERE LTRIM(RTRIM(ISNULL(fieldName'')))=''

but i didnt know how to put it in my select statement to return another value than an empty string.
0
troycomp
Asked:
troycomp
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check out the NULLIF function:


NULLIF(LTRIM(RTRIM(yourfield)), ''), '--N/A--')

Open in new window

0
 
Patrick MatthewsCommented:
SELECT CASE WHEN LTRIM(field) = '' OR field IS NULL THEN 'whatever' ELSE field END AS result
FROM some_table
0
 
troycompAuthor Commented:
@angellll:
I like your approach cause its short but it didnt work

@matthew:
This worked great. Im gonna but this inside a user defined function cause i have a lot of field to pull and this will be very useful
0
 
troycompAuthor Commented:
@angellll:

I got yours to work too:
ISNULL(NULLIF(LTRIM(RTRIM([ZIP_CODE_AN])), ''), '--N/A--')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, was missing the ISNULL() indeed...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now