6 Replies Latest reply: Jan 10, 2008 3:29 AM by 588568 RSS

    Help with a regular expression, please

    51034
      I'm never very good at regular expressions (it comes from being a Windows user, I think!), so all assistance would be appreciated.

      I have a table with a very silly design that has a column for account names that read something like

      Howard Rogers DIZWELL
      John Smith COMPANYB
      Fred Dalbert Eglehead PROMEOTICSPTYLTD

      I need to reduce the column so it just has the people's names, not their company codes. The codes are usually in upper case, but not always. I am reasonably confident that they are always the last word in the field, however.

      What bit of cunning regular expression cleverness will remove the last word from a field, please?!
        • 1. Re: Help with a regular expression, please
          247514
          Not a regular expression expert myself, something like this will work
          SYS@rman> l
            1* select regexp_replace('Howard Rogers DIZWELL',' [^ ]*$','') from dual
          SYS@rman> /

          REGEXP_REPLAC
          -------------
          Howard Rogers
          --
          SYS@rman> l
            1  select regexp_replace('Fred Dalbert Eglehead PROMEOTICSPTYLTD',' [^ ]*$','') from dual
          SYS@rman> /

          REGEXP_REPLACE('FREDD
          ---------------------
          Fred Dalbert Eglehead
          SYS@rman>
          CD has a good thread about Regular Expression in this forum
          Introduction to regular expressions ...
          • 2. Re: Help with a regular expression, please
            51034
            Thank you for that: perfect.

            Just one refinement: I might need to sort by the last code that you've just chopped off... so what's the change to get JUST that code returned?

            Sorry... I only just found out the possibility exists!
            • 3. Re: Help with a regular expression, please
              247514
              ok, that will be
              SYS@rman> create table reggy (val varchar2(50));

              Table created.

              SYS@rman> insert into reggy values ('Fred Dalbert Eglehead PROMEOTICSPTYLTD');

              1 row created.

              SYS@rman> insert into reggy values ('Howard Rogers DIZWELL');

              1 row created.

              SYS@rman> insert into reggy values ('John Smith COMPANYB');

              1 row created.

              SYS@rman> insert into reggy values ('some 1 else in  DIZWELL');

              1 row created.

              SYS@rman> commit;

              SYS@rman>
                1  select regexp_replace(val,' [^ ]*$','') name ,
                2  REGEXP_SUBSTR(val,' [^ ]*$') company from reggy
                3* order by company
              SYS@rman> /

              NAME                                     COMPANY
              ---------------------------------------- ------------------------------
              John Smith                                COMPANYB
              Howard Rogers                             DIZWELL
              some 1 else in                            DIZWELL
              Fred Dalbert Eglehead                     PROMEOTICSPTYLTD
              • 4. Re: Help with a regular expression, please
                Maran Viswarayar
                Ooops

                Great stuff!!!!

                I was reading the arcticle by CD ....but i think it wil take some time to understand

                Excellent

                Howard
                Appreciate you Profeesionalism , many experts dont like to ask questions in forums

                Bow my head
                • 5. Re: Help with a regular expression, please
                  51034
                  Wonderful. Thanks very much... I knew it could be done. Still looks like magic to me every time I see it, though!
                  • 6. Re: Help with a regular expression, please
                    588568
                    And for Olde Worlde folk (like me):
                    SQL> create table MY_TABLE ( MY_STRING varchar2(20));

                    Table created.

                    SQL> insert into MY_TABLE values('ABC DEF GHI');

                    1 row created.

                    SQL> SELECT substr(MY_STRING, instr(MY_STRING, ' ', -1) +1) "Last String" FROM MY_TABLE;

                    Last String
                    --------------------
                    GHI