| how to remove spaces in a column [message #22025] |
Wed, 18 September 2002 03:12  |
ksr
Messages: 112
Registered: January 2002
|
Senior Member |
|
|
Hi,
I have a table with single column "msg varchar2(50);"
with values like (i have given example of 3 rows)
-----------------------------------
hello how are you
The world is great
How are you dear
------------------------------
Now i want to remove the extra spaces between the words and keep only 1 space,
Now how can i do this,after the processing the value of the column should like below
-----------------------------------
hello how are you
The world is great
How are you dear
------------------------------
Any help is appreciated..
|
|
|
|
| Re: how to remove spaces in a column [message #22027 is a reply to message #22025] |
Wed, 18 September 2002 04:25  |
Aashish Sharma
Messages: 2
Registered: September 2002
|
Junior Member |
|
|
Hi!
Hope this following function solves your problem. If there is any bug feel free to inform me.
create or replace function remspace(inp varchar2) return varchar2
as
temp number(10) ;
result varchar2(100);
begin
result:=trim(inp);
loop
temp := instr(result,' ') ;
result := replace(result,' ',' ') ;
if temp = 0 then
exit ;
end if ;
end loop ;
return result ;
end ;
for e.g.
SQL> select remspace('hello world I love INDIA ') from dual;
REMSPACE('HELLOWORLDILOVEINDIA')
--------------------------------------------------------------------------------
hello world I love INDIA
|
|
|
|
|
|
| Re: how to remove spaces in a column [message #22042 is a reply to message #22025] |
Wed, 18 September 2002 22:05 |
Aashish Sharma
Messages: 2
Registered: September 2002
|
Junior Member |
|
|
Dear ,
Understand the concept, the reason of hang is the formmatting done by the ORAFAQ site , Just use replace command with two spaces what i mean is
result := replace(result,(2 spaces),(1 space))
Check out .
Regards,
Aashish
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
