Re: Parsing data for insert from one table to another

From: Vince <vopipari_at_ehealthcontracts.com>
Date: 7 Nov 2001 09:32:26 -0800
Message-ID: <8d71f979.0111070932.36ea6a1d_at_posting.google.com>

Aside from the issues brought up in other posts, the following could be used (it is not pretty):

SELECT
SUBSTR('Smith Jr., John Paul', 1, INSTR('Smith Jr., John Paul', ' ', 1, 1) - 1) AS last_name,
SUBSTR('Smith Jr., John Paul',

       INSTR('Smith Jr., John Paul', ' ', 1, 1) + 1, 
       INSTR('Smith Jr., John Paul', ',', 1, 1) -1 - INSTR('Smith Jr.,

John Paul', ' ', 1, 1)) AS suffix,
SUBSTR('Smith Jr., John Paul',

       INSTR('Smith Jr., John Paul', ',', 1, 1) + 2,
       INSTR('Smith Jr., John Paul', ' ', INSTR('Smith Jr., John

Paul', ',', 1, 1) + 2, 1) - 1 - INSTR('Smith Jr., John Paul', ',', 1, 1) ) AS first_name,
SUBSTR('Smith Jr., John Paul',

       INSTR('Smith Jr., John Paul', ' ', INSTR('Smith Jr., John Paul', ',', 1, 1), 2) + 1) AS middle_name from dual

Additionally, you could use a union depending on the suffix being a valid suffix with the second query using every thing from the first character to the comma being the last name, leaving only people with "Mc Donald Jr." as a problem:

SELECT everything above
from x
where INSTR('Smith Jr., John Paul', ',', 1, 1) -1 - INSTR('Smith Jr., John Paul', ' ', 1, 1)) in ('Jr.', 'Sr.',etc) UNION
SELECT everthing above (replaceing suffix with "NULL") from x
where INSTR('Smith Jr., John Paul', ',', 1, 1) -1 - INSTR('Smith Jr., John Paul', ' ', 1, 1)) not in ('Jr.', 'Sr.',etc)

chris_self_at_yahoo.com (Chris) wrote in message news:<52f2f187.0111051202.6b75aeee_at_posting.google.com>...
> Hi all -
>
> I would like to select a name field from one table (TableA) in the format:
>
> Smith Jr., John Paul
>
> and extract each piece to insert into fields on TableB
>
> First_Name
> Middle_Name
> Last_Name
> Suffix
>
> Does anyone know how I can do this with SQL?
>
> Thanks in advance.
Received on Wed Nov 07 2001 - 18:32:26 CET