Query to Eliminate multiple spaces [message #446046] |
Fri, 05 March 2010 05:42  |
tejasvi_ss
Messages: 22 Registered: February 2010 Location: Bangalore, India
|
Junior Member |
|
|
Hi,
I need to eliminate the blank spaces based on below conditions
Consider name column with a value as
Input : "sa c h in Te nd ulka r"
where "Sachin" is first name and "Tendulkar" is last name.
there is more than 1 space between sachin and tendulkar (here its not displaying properly)
Condition :Second name is seperated from first name with more than 1 spaces and others are with 1 black space. I need to get result as
Output:"sachin Tendulkar"
( there should be 1 blank space between first and last name in result.)
I need to get result using a SQL query.
Please help me...
Thanks,
Tejasvi
[Updated on: Fri, 05 March 2010 05:59] Report message to a moderator
|
|
|
Re: Query to Eliminate multiple spaces [message #446057 is a reply to message #446046] |
Fri, 05 March 2010 06:13   |
cookiemonster
Messages: 13964 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
SQL> WITH DATA AS (SELECT 'sa c h in Te nd ulka r' n FROM dual)
2 SELECT REPLACE (substr(n, 1, instr(n, ' ')), ' ', NULL)
3 ||' '
4 ||REPLACE (substr(n, instr(n, ' ')), ' ', NULL)FROM DATA;
REPLACE(SUBSTR(N
----------------
sachin Tendulkar
|
|
|
Re: Query to Eliminate multiple spaces [message #446059 is a reply to message #446046] |
Fri, 05 March 2010 06:15   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
A couple of regexp_replace() functions: one to remove spaces followed by non-space chars, and another to compress consecutive spaces to a single space.
select regexp_replace(
regexp_replace(
'R i ck y Po n t in g'
, ' ([^ ])', '\1'
)
, ' +', ' '
)
from dual
Ross Leishman
|
|
|
|