How do I check if a Sql server string is null or empty

sql-server-2005
digiguru picture digiguru · Dec 2, 2008 · Viewed 629.9k times · Source

I want to check for data, but ignore it if it's null or empty. Currently the query is as follows...

Select              
Coalesce(listing.OfferText, company.OfferText, '') As Offer_Text,         
from tbl_directorylisting listing  
 Inner Join tbl_companymaster company            
  On listing.company_id= company.company_id

But I want to get company.OfferText if listing.Offertext is an empty string, as well as if it's null.

What's the best performing solution?

Answer

Martin Ba picture Martin Ba · Jul 13, 2010

I think this: 

SELECT 
  ISNULL(NULLIF(listing.Offer_Text, ''), company.Offer_Text) AS Offer_Text
FROM ...

is the most elegant solution.

And to break it down a bit in pseudo code:

// a) NULLIF:
if (listing.Offer_Text == '')
  temp := null;
else
  temp := listing.Offer_Text; // may now be null or non-null, but not ''
// b) ISNULL:
if (temp is null)
  result := true;
else
  result := false;

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More