Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string (Oracle 11.2)
Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644426] Thu, 05 November 2015 08:03 Go to next message
orauser001
Messages: 13
Registered: April 2013
Location: us
Junior Member
Hi All,

I am new to use of regular expressions and needed some help with coming up with the right REGEX_REPLACE for our requirement explained below:

Requirement : The requirement in plain english is: Remove space between alphabetic tokens of length 1 within a string

Some examples would help clarify:
Example-1: "The I B M Corporation N Y US" should get converted to "The IBM Corporation NY US"

Example-2: "The P N CD FUND of J P R M" should get converted to "The PN CD FUND of JPRM"

Thanks in advance for your help


[EDITED by LF: applied [code] tags]

[Updated on: Fri, 06 November 2015 00:10] by Moderator

Report message to a moderator

Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644431 is a reply to message #644426] Thu, 05 November 2015 09:10 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
regexp_replace(regexp_replace('The P N CD FUND of J P R M ' , '([[:space:]].[[:space:]])','\1 '), '  ', '')

[Updated on: Thu, 05 November 2015 09:11]

Report message to a moderator

Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644432 is a reply to message #644431] Thu, 05 November 2015 09:38 Go to previous messageGo to next message
orauser001
Messages: 13
Registered: April 2013
Location: us
Junior Member
thanks, one issue though - this is giving:

"The PN CD FUND of JP RM"

where as what i am looking to get is

"The PN CD FUND of JPRM"

That is i would want to get no space between JP and RM, so I want this logic to work not just for two 1 char tokens - but any consecutive number of one char tokens separated by space
Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644434 is a reply to message #644432] Thu, 05 November 2015 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why "PN CD" and not "JP RM"?
Or why "JPRM" and not "PNCD" and not "PNCDFUND"?
How Oracle can differ all these?

Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644435 is a reply to message #644434] Thu, 05 November 2015 10:14 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Rule in this case appears to be very clear Michel and disallows the alternatives you ask about
Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644436 is a reply to message #644435] Thu, 05 November 2015 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes you are right I misread it, thanks to point me to this.

[Updated on: Thu, 05 November 2015 10:18]

Report message to a moderator

Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644437 is a reply to message #644434] Thu, 05 November 2015 10:18 Go to previous messageGo to next message
orauser001
Messages: 13
Registered: April 2013
Location: us
Junior Member
Sorry if the question was not clear, Let me try to explain

The original string is:

"The P N CD FUND of J P R M"

and the rule is : if there are two or more consecutive one character tokens in the input, remove space between them.

going by that:

P N fall in this definition so the space between them should be removed (CD is a two letter token so no change)
J P R M also falls in this category - note its not just two occurrences of single characters but four

So per that rule the output should be : "The PN CD FUND of JPRM"


Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644438 is a reply to message #644437] Thu, 05 November 2015 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with
  2    data as (
  3      select 'The P N CD FUND of J P R M' val from dual
  4      union all
  5      select 'The I B M Corporation N Y US' from dual
  6    )
  7  select val,
  8         substr(
  9           regexp_replace(
 10             regexp_replace(
 11               ' '||replace(val, ' ', '  ')||' ',
 12               ' ([^ ]) ', '\1'
 13             ),
 14             ' +[ $]+', ' '
 15           ),
 16           2
 17         ) new_val
 18  from data
 19  /
VAL                          NEW_VAL
---------------------------- ------------------------------
The P N CD FUND of J P R M   The PN CD FUND of JPRM
The I B M Corporation N Y US The IBM Corporation NY US

[Updated on: Thu, 05 November 2015 10:51]

Report message to a moderator

Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644439 is a reply to message #644438] Thu, 05 November 2015 14:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Or simpler:

with
    data as (
      select 'The P N CD FUND of J P R M' val from dual
      union all
      select 'The I B M Corporation N Y US' from dual
    )
select val,
       regexp_replace(
                      regexp_replace(
                                     replace(val, ' ', '  '),
                                     '(^| )([^ ])( |$)', '\2'
                                    ),
                      '  +',
                      ' '
                     ) new_val
  from data
/

VAL                          NEW_VAL
---------------------------- -------------------------
The P N CD FUND of J P R M   The PN CD FUND of JPRM
The I B M Corporation N Y US The IBM Corporation NY US

SQL> 


However besides removing spaces between two or more consecutive one character tokens your solution will also replace multiple spaces between other tokens with a single space. Most likely OP will be OK with that, but just though it is worth mentioning:

with
    data as (
      select 'The P N CD    FUND    of J P R M' val from dual
      union all
      select 'The    I B M    Corporation N Y    US' from dual
    )
  select val,
         substr(
           regexp_replace(
             regexp_replace(
               ' '||replace(val, ' ', '  ')||' ',
               ' ([^ ]) ', '\1'
             ),
             ' +[ $]+', ' '
           ),
           2
         ) new_val
  from data
/


VAL                                   NEW_VAL
------------------------------------- ---------------------------
The P N CD    FUND    of J P R M      The PN CD FUND of JPRM
The    I B M    Corporation N Y    US The IBM Corporation NY US

SQL> 


SY.

[Updated on: Thu, 05 November 2015 14:05]

Report message to a moderator

Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644445 is a reply to message #644432] Fri, 06 November 2015 02:24 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
orauser001 wrote on Thu, 05 November 2015 15:38
thanks, one issue though - this is giving:

"The PN CD FUND of JP RM"

where as what i am looking to get is

"The PN CD FUND of JPRM"

That is i would want to get no space between JP and RM, so I want this logic to work not just for two 1 char tokens - but any consecutive number of one char tokens separated by space

Ahhh, apologies, didn't see my mistake there. Glad you got more talented people to sort it for you Smile
Re: Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644487 is a reply to message #644445] Sat, 07 November 2015 23:00 Go to previous message
orauser001
Messages: 13
Registered: April 2013
Location: us
Junior Member
Big Thanks to all of you for helping with the exact solution i needed !!!
Previous Topic: find updated table name
Next Topic: sql query help
Goto Forum:
  


Current Time: Tue Mar 19 04:42:01 CDT 2024