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
584 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 142

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 142

Expert Comment

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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 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