• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 29
  • Last Modified:

Split a field to two fields then do a when statement if father field is null use mother field to populate the Person1LastName and Person1FirstName fields

So if the father is null use mother for person1fields and/or if mother is null use father for person1fields. BUT if both are NULL, use either ParGuar_3_custodialParent or Parguar_4_custodialParent fields; of using the same substr function like the mother/father field.

I am having trouble writing the statement.... if the father field is null then use the mother field. But as you can see, I also need to take the mother/father field and split in to two fields (Person1LastName andPerson1FirstName)

  SUBSTR (s.father, 1, INSTR(s.father, ',', 1, 1) -1) as Person1LastName,
  SUBSTR (s.father, (INSTR(s.father, ',', 1, 1)+1)) as Person1FirstName,
  SUBSTR (s.mother, 1, INSTR(s.mother, ',', 1, 1) -1) as Person1LastName,
  SUBSTR (s.mother, (INSTR(s.mother, ',', 1, 1)+1)) as Person1FirstName,
Stephanie kominek
Stephanie kominek
2 Solutions
Replace s.father with coalesce(s.father, s.mother)
Bill PrewCommented:
Not sure if/how you want to handle if all 4 of those are NULL, but here is an approach that checks each one in order until it finds the first non NULL one which it chooses.

SUBSTR (COALESCE(s.father, s.mother, s.ParGuar_3_custodialParent, s.Parguar_4_custodialParent), 1, INSTR(COALESCE(s.father, s.mother, s.ParGuar_3_custodialParent, s.Parguar_4_custodialParent), ',', 1, 1) -1) as Person1LastName,
SUBSTR (COALESCE(s.father, s.mother, s.ParGuar_3_custodialParent, s.Parguar_4_custodialParent), (INSTR(COALESCE(s.father, s.mother, s.ParGuar_3_custodialParent, s.Parguar_4_custodialParent), ',', 1, 1)+1)) as Person1FirstName,

Open in new window

Stephanie kominekAuthor Commented:
Thank you both for assisting. Your solutions were exactly what I needed to complete my query statement. It is working as expected. Thank you!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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