Home » SQL & PL/SQL » SQL & PL/SQL » Query to Eliminate multiple spaces (SQL)
Query to Eliminate multiple spaces [message #446046] Fri, 05 March 2010 05:42 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Query to Eliminate multiple spaces [message #446064 is a reply to message #446046] Fri, 05 March 2010 06:18 Go to previous message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
One of these days I'm going to have to learn how to use regular expressions properly.
Previous Topic: what is the reason?
Next Topic: compare two table data
Goto Forum:
  


Current Time: Wed Dec 07 03:21:10 CST 2016

Total time taken to generate the page: 0.15029 seconds