[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

t-sql coalesce problem with space in column value

t-sql coalesce problem with space in column value

I have a SQL select statement using coalesce as following

SELECT  COALESCE (A.FIRST_NAME, B.FIRST_NAME) AS FIRST_NAME,
      COALESCE (A.LAST_NAME, B.LAST_NAME) AS LAST_NAME
FROM TEST_A A
 INNER JOIN TEST_B B
 ON A.ID = B.ID

It works as long as the A table column if is null, it shows the b table data.  
My problem is table A has value of '' (space, not null value).

What can I do to fix this?
0
jfreeman2010
Asked:
jfreeman2010
  • 2
2 Solutions
 
JestersGrindCommented:
Use NULLIF.  NULLIF compares two values and if they are equal returns NULL.  So, you would do something like NULLIF(Your "A" Column, '').  If the column is an empty string it returns NULL.

Greg

0
 
wdosanjosCommented:
As @JestersGrind already indicated, the query would look like this:

SELECT  COALESCE (NULLIF(A.FIRST_NAME,''), B.FIRST_NAME) AS FIRST_NAME,
      COALESCE (NULLIF(A.LAST_NAME,''), B.LAST_NAME) AS LAST_NAME
FROM TEST_A A
 INNER JOIN TEST_B B
 ON A.ID = B.ID
0
 
jfreeman2010Author Commented:
Thank you for the response.  try the nullif, did not work.

What I am looking is if A.last_name is null or space or '', return value from  b.last_name.

thanks,
0
 
jfreeman2010Author Commented:
thanks!!!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now