4 Replies Latest reply: Feb 8, 2012 2:34 AM by 915879 RSS

    [name - 2ndName Firstname] -- Seperate both names from the firstname

    915879
      Hi,

      I have a field "NAME_IN", which sometimes not only includes the name, but also the firstname. I can easily get the name and firstname seperated by using: substr, regexp (to get the space out), etc...
      But the problem is that there's not just 1 name, but 2 names (+ first name), which are linked by a "-". example: Simpson-Bouvier Marge

      Still, no problem there normally. But around the "-" there are spaces as well; so with the previous example, it becomes: Simpson - Bouvier Marge.
      If I try use substrings and regexpr now, to get the first name before the space, I obviously get "Simpson", instead of "Simpson - Bouvier".

      So the code I'm using atm is:
      substr(name_in,1,instr(name_in,' ')-1)

      Anyone who can help me with this?
        • 1. Re: [name - 2ndName Firstname] -- Seperate both names from the firstname
          riedelme
          What version of the database are you on?

          If supported regexp_substr() or regexp_replace might do the job. Define your pattern, perform the substituion, and the job might be easier (except for the bit about working with regular expressions) :)
          • 2. Re: [name - 2ndName Firstname] -- Seperate both names from the firstname
            Frank Kulash
            Hi,

            Here's one way:
            WITH     sample_data     AS
            (
                 SELECT     'Simpson -Bouvier Marge'     AS name     FROM dual     UNION ALL
                 SELECT     'FitzGerald Francis Scott Key'          FROM dual     UNION ALL
                 SELECT  'Baring-Gould Sabine'                 FROM dual     UNION ALL
                 SELECT     'Jeunet Jean-Pierre'               FROM dual     UNION ALL
                 SELECT     'Fischer  -  Price'               FROM dual
            )
            SELECT     REGEXP_SUBSTR ( name
                                 , '^[[:alpha:]]+( *- *[[:alpha:]]+)?'
                                 )               AS family_name
            ,     REGEXP_REPLACE ( name
                             , '^[[:alpha:]]+( *- *[[:alpha:]]+)?'
                             )               AS given_name
            FROM    sample_data
            ;
            Output:
            FAMILY_NAME                  GIVEN_NAME
            ---------------------------- ----------------------------
            Simpson -Bouvier              Marge
            FitzGerald                    Francis Scott Key
            Baring-Gould                  Sabine
            Jeunet                        Jean-Pierre
            Fischer  -  Price
            Notice the sub-expression in parentheses
            ( *- *[[:alpha:]]+)?
            which can be read as a hyphen followed by 1 or more letters. There may be any number of spaces before and/or after the hyphen, but the hyphen must be there. If there is no hyphen, then patten is not found. The '?' after the parenthese means that that entire sub-pattern is optional; if it is found, include it, but if it is not found, just match the rest olf the pattern.
            • 3. Re: [name - 2ndName Firstname] -- Seperate both names from the firstname
              915879
              Hi, thanks for the reacting so fast.

              The given solutions seem to do what I want them to do; but since I'm not in the office anymore atm, i'll try it out tomorrow.

              Thanks!

              Greets
              • 4. Re: [name - 2ndName Firstname] -- Seperate both names from the firstname
                915879
                Just tested it, and that was what I wanted!

                thanks again