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
598 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 250 total points
ID: 22659894
please check out the NULLIF function:


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

Open in new window

0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

749 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