?
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
?
613 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Viewers will learn how the fundamental information of how to create a table.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 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