| multiple spaces [message #371296] |
Fri, 29 September 2000 14:44  |
Latha
Messages: 9
Registered: September 2000
|
Junior Member |
|
|
my table has a column which has multiple spaced strings for e.g >
IV_NM
-----
ABC(multiple spaces)TRUST(multiple spaces)COMPANY
ABC(one space)INSRANCE(one space)INC.
RDB(multiple spaces)CONSULTING(one space)INC.
XYZ(one space)TEST(one space)COMPANY
ARIAL(multiple spaces)INSURANCE(multiple spaces)COMPANY
and so on
I have to delete all the extra spaces in between the words in the column so that my column should look like
IV_NM
-----
ABC(one space)TRUST(one space)COMPANY
ABC(one space)INSRANCE(one space)INC.
RDB(one space)CONSULTING(one space)INC.
XYZ(one space)TEST(one space)COMPANY
ARIAL(one space)INSURANCE(one space)COMPANY
How can I do this in a script? please help
|
|
|
|
| Re: multiple spaces [message #371299 is a reply to message #371296] |
Sat, 30 September 2000 01:04  |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
try this one.
name will be the column with multiple spaces.you can change the code as per your requirement.
declare
i number:=0;
ab varchar2(100):=' ';
var1 varchar2(1);
name varchar2(50):='rhe kj dk jdfjh';
begin
for i in 1..length(name)
loop
var1:=substr(name,i,1);
if var1!= ' ' then
ab:=ab||var1;
else
if substr(name,i-1,1)!= ' ' then
ab:=ab||' ';
end if;
end if;
end loop;
dbms_output.put_line(ab);
end;
Rajendra
|
|
|
|
| Re: multiple spaces [message #371301 is a reply to message #371296] |
Sat, 30 September 2000 01:40 |
Naseer
Messages: 5
Registered: September 2000
|
Junior Member |
|
|
Hello,
Try this
1 SELECT
2 REPLACE(
3 REPLACE(
4 REPLACE(
5 REPLACE('ABC PVT LTD.',
6 ' ',' '),
7 ' ',' '),
8 ' ',' '),
9 ' ',' ')
10* FROM DUAL ;
greetings
|
|
|
|
| Re: multiple spaces [message #371302 is a reply to message #371296] |
Sat, 30 September 2000 02:39 |
Rajendra
Messages: 23
Registered: September 2000
|
Junior Member |
|
|
Try this also-
declare
i number:=0;
ab varchar2(100):=' ';
var1 varchar2(1);
na table_name.field_name%type;
cursor cur1 is select field_name from table_name;
begin
open cur1;
loop
fetch cur1 into na;
exit when cur1%notfound;
for i in 1..length(na)
loop
var1:=substr(na,i,1);
if var1!= ' ' then
ab:=ab||var1;
else
if substr(na,i-1,1)!= ' ' then
ab:=ab||' ';
end if;
end if;
end loop;
dbms_output.put_line(ab);
ab:=' ';
end loop;
end;
greetings......
Rajendra
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
