>Hi Philip,

>Thanks for your valuable help. Sorry to hear you're not feeling well. I will use your suggestion
>and example. I ended up parsing (like you did) but to a child table. So all the names are just
>sitting there, both human and pet. So I should be able to iterate through, using your example, or
>maybe a For Into Do loop in a SP.
>
>(This way, I save a few CPU cycles and did not have to change the original table design)

Why not add AFTER INSERT/DELETE/UPDATE triggers to your original table, so that when things are inserted/updated/deleted, then things are parsed into separate child tables. Then, your original question can be solved by simple SQL:

SELECT DISTINCT OT.USR_ID, OT.ID_NMBR, OT.MY_FAMILY_HUMAN_NAMES, OT.MY_FAMILY_PET_NAMES
FROM ORIGINAL_TABLE_NOT_CHANGED OT
JOIN NORMALIZED_FAMILY_PET_NAMES PN
ON OT.USR_ID = PN.USR_ID
AND OT.ID_NMBR = PN.ID_NMBR
JOIN NORMALIZED_HUMAN_NAMES HN
ON PN.NAME = HN.NAME

It is also possible to solve your problem simply using a recursive CTE, though it is likely to be very slow on large tables:

WITH RECURSIVE EXTRACT_PET_NAME(USR_ID, ID_NMBR, PET_NAMES, PET_NAME)
AS (
SELECT USR_ID, ID_NMBR,
CASE
WHEN POSITION(' ' IN MY_FAMILY_PET_NAMES) > 0 THEN
SUBSTRING(MY_FAMILY_PET_NAMES FROM POSITION(' ' IN MY_FAMILY_PET_NAMES)+1 FOR LENGTH(MY_FAMILY_PET_NAMES))
END,
CASE
WHEN POSITION(' ' IN MY_FAMILY_PET_NAMES) > 0 THEN
SUBSTRING(MY_FAMILY_PET_NAMES FROM 1 FOR POSITION(' ' IN MY_FAMILY_PET_NAMES) - 1)
ELSE
MY_FAMILY_PET_NAMES
END
FROM ORIGINAL_TABLE_NOT_CHANGED
UNION ALL
SELECT USR_ID, ID_NMBR,
CASE
WHEN POSITION(' ' IN PET_NAMES) > 0 THEN
SUBSTRING(PET_NAMES FROM POSITION(' ' IN PET_NAMES)+1 FOR LENGTH(PET_NAMES))
END,
CASE
WHEN POSITION(' ' IN PET_NAMES) > 0 THEN
SUBSTRING(PET_NAMES FROM 1 FOR POSITION(' ' IN PET_NAMES) - 1)
ELSE
PET_NAMES
END
FROM EXTRACT_PET_NAME
WHERE PET_NAMES > '',
EXTRACT_HUMAN_NAME(HUMAN_NAMES, HUMAN_NAME)
AS (
SELECT
CASE
WHEN POSITION(' ' IN MY_FAMILY_HUMAN_NAMES) > 0 THEN
SUBSTRING(MY_FAMILY_HUMAN_NAMES FROM POSITION(' ' IN MY_FAMILY_HUMAN_NAMES)+1 FOR LENGTH(MY_FAMILY_HUMAN_NAMES))
END,
CASE
WHEN POSITION(' ' IN MY_FAMILY_HUMAN_NAMES) > 0 THEN
SUBSTRING(MY_FAMILY_HUMAN_NAMES FROM 1 FOR POSITION(' ' IN MY_FAMILY_HUMAN_NAMES) - 1)
ELSE
MY_FAMILY_HUMAN_NAMES
END
FROM ORIGINAL_TABLE_NOT_CHANGED
UNION ALL
SELECT
CASE
WHEN POSITION(' ' IN HUMAN_NAMES) > 0 THEN
SUBSTRING(HUMAN_NAMES FROM POSITION(' ' IN HUMAN_NAMES)+1 FOR LENGTH(HUMAN_NAMES))
END,
CASE
WHEN POSITION(' ' IN HUMAN_NAMES) > 0 THEN
SUBSTRING(HUMAN_NAMES FROM 1 FOR POSITION(' ' IN HUMAN_NAMES) - 1)
ELSE
HUMAN_NAMES
END
FROM EXTRACT_HUMAN_NAME
WHERE HUMAN_NAMES > ''

SELECT DISTINCT OT.USR_ID, OT.ID_NMBR, OT.MY_FAMILY_HUMAN_NAMES, OT.MY_FAMILY_PET_NAMES
FROM ORIGINAL_TABLE_NOT_CHANGED OT
JOIN EXTRACT_PET_NAME PN
ON OT.USR_ID = PN.USR_ID
AND OT.ID_NMBR = PN.ID_NMBR
JOIN EXTRACT_HUMAN_NAME HN
ON HN.HUMAN_NAME = PN.PET_NAME

HTH,
Set