SQL Server function to check for null and spaces

Milind Agarwal
Milind Agarwal used Ask the Experts™
on
Hello Experts,
I am using ISNULL function to get the column to populate from two columns which do not have a Null.

Example ISNULL(ColumnA,ColumnB) as ABC  Polulates ColumnB Value in the Column 'ABC' when columnA is null. Now we have some rows in columnA that is empty space like ' ' instead of a Null.  What changes do I need to make to my SQL so it will populate ColumnB value in ABC when the ColumnA is an empty space instead of a null.

Issue is below:

ColumnA    ColumnB     ABC
  NULL           Hello         Hello
  '  '                 Hello          '   '

Expected Solution

ColumnA    ColumnB     ABC
  NULL           Hello         Hello
  '  '                 Hello         Hello

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
not sure if trimmed white space will return null but you can try ISNULL(rtrim(ColumnA),ColumnB) as ABC

or

 ISNULL(NULLIF(ColumnA, ' '),ColumnB) as ABC
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
https://msdn.microsoft.com/en-us/library/ms190349.aspx

I think you can do this.

SELECT ColumnA , ColumnB , 
     CASE 
          WHEN isnull(ColumnA , '') = '' THEN ColumnB 
          WHEN ColumnA = '' THEN ColumnB 
     END AS ABC
FROM MyTable

Open in new window

Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
You may want to add an ELSE so the TEST column is not null

SELECT ColumnA , ColumnB , 
     CASE 
          WHEN isnull(ColumnA , '') = '' THEN ColumnB 
          WHEN ColumnA = '' THEN ColumnB 
          ELSE 'N/A'
     END AS ABC
FROM MyTable

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial