Search code examples
sqloracle-databaseregexp-replace

Invalid Number is returned by Regexp_replace

I want to remove titles like Dr., Mr. Jr. from name. Regexp_replace should Search and remove Dr. Mr. Jr and search should be case insensitive, final output should not have . or , or extra spaces in name.

select regexp_replace('DR. Mr. Mr Dr Radha Jr Jr. Krishnan Jr.','(Dr|Mr|Jr)(\W|\w|$|,)',1,0,'i') 
from dual

I am getting invalid number error.

Solution

  • Your solution lacks in two aspects.

    The more important is that you may cut of the leading "titles" from names:

    select regexp_replace('DR. Mr. Mr Dr Dradha Jr Jr. Mrishnan Jr.','(Dr|Mr|Jr)(\W|\w|$|,)','',1,0,'i') 
from dual

    which returns a not expected result

    dha  shnan

    The remedy is to allow only the non word characters (\W) and end of line ($) after the title.

    If you enforce optional space or begin of the line (^) before the title, you get rid of the second problem the spaces left after the remove of titles

    select regexp_replace('DR. Mr. Mr Dr Dradha Jr Jr. Mr, Mrishnan Jr.',
  '( *|^)(Dr|Mr|Jr)(\W|$)','',1,0,'i') 
from dual

Dradha Mrishnan