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 kominekAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.