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

Question

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

User · Answer

You can use ISNULL and check the answer against the known output   SELECT case when ISNULL col1           then    else col1 END AS COL1 FROM TEST

User · Answer

In SQL Server 2012 you have IIF  e g you can use it like   SELECT IIF field IS NULL  1  0  AS IsNull   The same way you can check if field is empty

User · Answer

You can use ISNULL and check the answer against the known output   SELECT case when ISNULL col1           then    else col1 END AS COL1 FROM TEST

User · Answer

SELECT    CASE WHEN LEN listing OfferText   gt  0 THEN listing OfferText          ELSE COALESCE Company OfferText      END     AS Offer Text          In this example  if listing OfferText is NULL  the LEN   function should also return NULL  but that s still not   0   Update   I ve learned some things in the 5 1 2 years since posting this  and do it much differently now   COALESCE NULLIF listing OfferText      Company OfferText        This is similar to the accepted answer  but it also has a fallback in case Company OfferText is also null  None of the other current answers using NULLIF   also do this

User · Answer

Here s a solution  but I don t know if it s the best      Select               Coalesce Case When Len listing Offer Text    0 Then Null Else listing Offer Text End  company Offer Text      As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

Use the LEN function to check for null or empty values  You can just use LEN  SomeVarcharParm    0  This will return false if the value is NULL      or      This is because LEN NULL  returns NULL and NULL   0 returns false  Also  LEN      returns 0  See for yourself run   SELECT   CASE WHEN NULL  gt  0 THEN  NULL  gt  0   true  ELSE  NULL  gt  0   false  END   CASE WHEN LEN NULL   gt  0 THEN  LEN NULL    true  ELSE  LEN NULL    false  END   CASE WHEN LEN      gt  0 THEN  LEN        gt  0   true  ELSE  LEN        gt  0   false  END   CASE WHEN LEN       gt  0 THEN  LEN         gt  0   true  ELSE  LEN         gt  0   false  END   CASE WHEN LEN   test     gt  0 THEN  LEN    test      gt  0   true  ELSE  LEN    test      gt  0   false  END

User · Answer

When dealing with VARCHAR NVARCHAR data most other examples treat white-space the same as empty string which is equal to C  function IsNullOrWhiteSpace  This version respects white-space and works the same as the C  function IsNullOrEmpty  IIF ISNULL DATALENGTH val   0    0  whenTrueValue  whenFalseValue   Simple test  SELECT       quot     val     quot   AS  StrValue       IIF ISNULL DATALENGTH val   0    0   TRUE    FALSE   AS IsNullOrEmpty FROM   VALUES       NULL                                 a           a      S  val

User · Answer

Column name  IS NULL OR LEN RTRIM LTRIM  Column name       0

User · Answer

Use the LEN function to check for null or empty values  You can just use LEN  SomeVarcharParm    0  This will return false if the value is NULL      or      This is because LEN NULL  returns NULL and NULL   0 returns false  Also  LEN      returns 0  See for yourself run   SELECT   CASE WHEN NULL  gt  0 THEN  NULL  gt  0   true  ELSE  NULL  gt  0   false  END   CASE WHEN LEN NULL   gt  0 THEN  LEN NULL    true  ELSE  LEN NULL    false  END   CASE WHEN LEN      gt  0 THEN  LEN        gt  0   true  ELSE  LEN        gt  0   false  END   CASE WHEN LEN       gt  0 THEN  LEN         gt  0   true  ELSE  LEN         gt  0   false  END   CASE WHEN LEN   test     gt  0 THEN  LEN    test      gt  0   true  ELSE  LEN    test      gt  0   false  END

User · Answer

This caters for spaces as well    len rtrim ltrim isnull MyField          0

User · Answer

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

User · Answer

To check if variable is null or empty use this   IF LEN ISNULL  var         0     -- Is empty or NULL ELSE     -- Is not empty and is not NULL

User · Answer

Column name  IS NULL OR LEN RTRIM LTRIM  Column name       0

User · Answer

I know this is an old thread but I just saw one of the earlier posts above and it is not correct   If you are using LEN      to determine whether the field is NULL or EMPTY then you need to use it as follows      WHEN LEN ISNULL MyField        lt  1 THEN NewValue

User · Answer

This simple combination of COALESCE and NULLIF should do the trick   SELECT                Coalesce NULLIF listing OfferText       company OfferText  As Offer Text       Note  Add another empty string as the last COALESCE argument if you want the statement to return an empty string instead of NULL if both values are NULL

User · Answer

To prevent the records with Empty or Null value in SQL result  we can simply add             WHERE Column name       or  null

User · Answer

Select               CASE     WHEN listing OfferText is null or listing OfferText      THEN company OfferText     ELSE COALESCE Company OfferText      END As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

I know this is an old thread but I just saw one of the earlier posts above and it is not correct   If you are using LEN      to determine whether the field is NULL or EMPTY then you need to use it as follows      WHEN LEN ISNULL MyField        lt  1 THEN NewValue

User · Answer

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

User · Answer

SELECT    CASE WHEN LEN listing OfferText   gt  0 THEN listing OfferText          ELSE COALESCE Company OfferText      END     AS Offer Text          In this example  if listing OfferText is NULL  the LEN   function should also return NULL  but that s still not   0   Update   I ve learned some things in the 5 1 2 years since posting this  and do it much differently now   COALESCE NULLIF listing OfferText      Company OfferText        This is similar to the accepted answer  but it also has a fallback in case Company OfferText is also null  None of the other current answers using NULLIF   also do this

User · Answer

Here s a solution  but I don t know if it s the best      Select               Coalesce Case When Len listing Offer Text    0 Then Null Else listing Offer Text End  company Offer Text      As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

Here s a solution  but I don t know if it s the best      Select               Coalesce Case When Len listing Offer Text    0 Then Null Else listing Offer Text End  company Offer Text      As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

Select               Coalesce NullIf listing OfferText       NullIf company OfferText           As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

Select               CASE     WHEN listing OfferText is null or listing OfferText      THEN company OfferText     ELSE COALESCE Company OfferText      END As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

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

User · Answer

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

User · Answer

Select               CASE     WHEN listing OfferText is null or listing OfferText      THEN company OfferText     ELSE COALESCE Company OfferText      END As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

When dealing with VARCHAR NVARCHAR data most other examples treat white-space the same as empty string which is equal to C  function IsNullOrWhiteSpace  This version respects white-space and works the same as the C  function IsNullOrEmpty  IIF ISNULL DATALENGTH val   0    0  whenTrueValue  whenFalseValue   Simple test  SELECT       quot     val     quot   AS  StrValue       IIF ISNULL DATALENGTH val   0    0   TRUE    FALSE   AS IsNullOrEmpty FROM   VALUES       NULL                                 a           a      S  val

User · Answer

Select               CASE     WHEN listing OfferText is null or listing OfferText      THEN company OfferText     ELSE COALESCE Company OfferText      END As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

Here s a solution  but I don t know if it s the best      Select               Coalesce Case When Len listing Offer Text    0 Then Null Else listing Offer Text End  company Offer Text      As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

Column name         excludes Nulls and empty strings  There is a space between the single quotes

User · Answer

Here is another solution   SELECT Isnull Nullif listing offertext       company offertext  AS offer text   FROM   tbl directorylisting listing         INNER JOIN tbl companymaster company           ON listing company id   company company id

User · Answer

To check if variable is null or empty use this   IF LEN ISNULL  var         0     -- Is empty or NULL ELSE     -- Is not empty and is not NULL

User · Answer

This simple combination of COALESCE and NULLIF should do the trick   SELECT                Coalesce NULLIF listing OfferText       company OfferText  As Offer Text       Note  Add another empty string as the last COALESCE argument if you want the statement to return an empty string instead of NULL if both values are NULL

User · Answer

You can use ISNULL and check the answer against the known output   SELECT case when ISNULL col1           then    else col1 END AS COL1 FROM TEST

User · Answer

Here is another solution   SELECT Isnull Nullif listing offertext       company offertext  AS offer text   FROM   tbl directorylisting listing         INNER JOIN tbl companymaster company           ON listing company id   company company id

User · Answer

In SQL Server 2012 you have IIF  e g you can use it like   SELECT IIF field IS NULL  1  0  AS IsNull   The same way you can check if field is empty

User · Answer

This caters for spaces as well    len rtrim ltrim isnull MyField          0

User · Answer

Column name         excludes Nulls and empty strings  There is a space between the single quotes

User · Answer

To prevent the records with Empty or Null value in SQL result  we can simply add             WHERE Column name       or  null

User · Answer

this syntax    SELECT   FROM tbl directorylisting listing WHERE  civilite etudiant IS NULL    worked for me in Microsoft SQL Server 2008  SP3

User · Answer

SELECT    CASE WHEN LEN listing OfferText   gt  0 THEN listing OfferText          ELSE COALESCE Company OfferText      END     AS Offer Text          In this example  if listing OfferText is NULL  the LEN   function should also return NULL  but that s still not   0   Update   I ve learned some things in the 5 1 2 years since posting this  and do it much differently now   COALESCE NULLIF listing OfferText      Company OfferText        This is similar to the accepted answer  but it also has a fallback in case Company OfferText is also null  None of the other current answers using NULLIF   also do this

User · Answer

Select               Coalesce NullIf listing OfferText       NullIf company OfferText           As Offer Text           from tbl directorylisting listing    Inner Join tbl companymaster company               On listing company id  company company id

User · Answer

this syntax    SELECT   FROM tbl directorylisting listing WHERE  civilite etudiant IS NULL    worked for me in Microsoft SQL Server 2008  SP3

User · Answer

SELECT    CASE WHEN LEN listing OfferText   gt  0 THEN listing OfferText          ELSE COALESCE Company OfferText      END     AS Offer Text          In this example  if listing OfferText is NULL  the LEN   function should also return NULL  but that s still not   0   Update   I ve learned some things in the 5 1 2 years since posting this  and do it much differently now   COALESCE NULLIF listing OfferText      Company OfferText        This is similar to the accepted answer  but it also has a fallback in case Company OfferText is also null  None of the other current answers using NULLIF   also do this

User · Answer

You can use ISNULL and check the answer against the known output   SELECT case when ISNULL col1           then    else col1 END AS COL1 FROM TEST

[sql-server-2005] How do I check if a Sql server string is null or empty

Examples related to sql-server-2005