Oracle Regular Expression - Remove space between alphabetic tokens of length 1 within a string [message #644426] |
Thu, 05 November 2015 08:03 |
|
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 #644438 is a reply to message #644437] |
Thu, 05 November 2015 10:49 |
|
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 |
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
|
|
|
|
|