[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-10-07
5
Medium Priority
?
623 Views
Last Modified: 2012-08-13
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
Comment
Question by:troycomp
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22659894
please check out the NULLIF function:


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

Open in new window

0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 22659899
SELECT CASE WHEN LTRIM(field) = '' OR field IS NULL THEN 'whatever' ELSE field END AS result
FROM some_table
0
 

Author Comment

by:troycomp
ID: 22660059
@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
 

Author Comment

by:troycomp
ID: 22660072
@angellll:

I got yours to work too:
ISNULL(NULLIF(LTRIM(RTRIM([ZIP_CODE_AN])), ''), '--N/A--')
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22660150
sorry, was missing the ISNULL() indeed...
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question