5 Replies Latest reply on Jan 20, 2012 9:10 AM by BluShadow

    Proper Way to Do This; Regex? A function?

    911647
      My problem is actually to take the name fields (lastname, firstname, middlename) in the database (all in Uppercase) and correctly display them. For instance:
      O'IRISHNAME --> as O'Irishname and
      MACNAME as MacName and MCNAME as McName and
      LONGHYPHENATED-NAME as Longhyphenated-Name and
      LASTNAME/PARTTWO as Lastname/Parttwo and lastly
      LNAME PARTTWO as Lname Parttwo.

      Using the java.util.regex API seems to be the way to go.

      1) How do I make the corrections IN the database rather than say in a file? using a function?
      2) The apostrophe and the dash seem to be easy enough but the space (&160; or ) wont return in a query. No luck with the / either.

      Explanation please
        • 1. Re: Proper Way to Do This; Regex? A function?
          Marwim
          Hello,

          Most cases can be done with INITCAP
          select initcap('LASTNAME/PARTTWO') x from dual;
          X                
          ---------------- 
          Lastname/Parttwo 
          For the Mac and Mc you can use REGEXP. this is available in SQL too.

          Regards
          Marcus
          1 person found this helpful
          • 2. Re: Proper Way to Do This; Regex? A function?
            BluShadow
            SQL> ed
            Wrote file afiedt.buf
            
              1  with t as (select 'O''IRISHNAME' as nm from dual union all
              2             select 'MACNAME' from dual union all
              3             select 'MCNAME' from dual union all
              4             select 'LONGHYPHENATED-NAME' from dual union all
              5             select 'LASTNAME/PARTTWO' from dual union all
              6             select 'LNAME PARTTWO' from dual)
              7  --
              8  -- end of test data
              9  --
             10  select case when regexp_like(nm, '^O''.') then 'O'''||InitCap(substr(nm,3))
             11              when regexp_like(nm, '^MC') then 'Mc'||InitCap(substr(nm,3))
             12              when regexp_like(nm, '^MAC') then 'Mac'||InitCap(substr(nm,4))
             13         else InitCap(nm)
             14         end as nm
             15* from t
            SQL> /
            
            NM
            -------------------
            O'Irishname
            MacName
            McName
            Longhyphenated-Name
            Lastname/Parttwo
            Lname Parttwo
            
            6 rows selected.
            it could probably be simplified to all be done with regexp, but I'm off home hehe!
            • 3. Re: Proper Way to Do This; Regex? A function?
              kendenny
              The 'Mac' one is problematic because there are last names that begin with "Mac" that should not capitalize the fourth letter. A quick query into the employee database where I work shows the following names that would be problems
              Machado, Machate, Macri, Maceda, Macaulay, Maccioni.
              1 person found this helpful
              • 4. Re: Proper Way to Do This; Regex? A function?
                riedelme
                kendenny wrote:
                The 'Mac' one is problematic because there are last names that begin with "Mac" that should not capitalize the fourth letter. A quick query into the employee database where I work shows the following names that would be problems
                Machado, Machate, Macri, Maceda, Macaulay, Maccioni.
                Seems like I spent 90% of time on special cases that affect 1% of the data :(

                If you can't find a pattern you'll have to hard-code the special cases using IF logic. Inconvenient but doable, something like
                IF conversion_result = 'BadValue' THEN
                
                    conversion_result = 'Badvalue';
                
                ELSIF ...
                or better put the bad and conversion values in a table to avoid hard-coding and check/convert the values using a lookup
                1 person found this helpful
                • 5. Re: Proper Way to Do This; Regex? A function?
                  BluShadow
                  kendenny wrote:
                  The 'Mac' one is problematic because there are last names that begin with "Mac" that should not capitalize the fourth letter. A quick query into the employee database where I work shows the following names that would be problems
                  Machado, Machate, Macri, Maceda, Macaulay, Maccioni.
                  or Mackintosh. :)
                  1 person found this helpful