remove word from column

[Ratzie]

I did an opps and ran the update on the wrong database.
Now I need to remove:
speed test
or
(speed test)
from the address column

SO I need to keep the actual address but remove what I said above.

[Rodney Hills]

With perl you can do-
perl -p -e 's/\(?speed test\)?//' outfile

HTH

-- Rod Hills

[Ranjith_5]

Hi,

Can be done with SED as well.

#sed -e 's/speed test//g' file_name > new_file

Regards,
Syam

[Ratzie]

I need to do this in oracle

[Ranjith_5]

you mean oracle table?

Regards,
Syam

[Ranjith_5]

Have you committed the changes ? What SQL statement did u use to insert the value "speed test" ?

Regards,
Syam

[Ranjith_5]

Hi,

I dont know aht is your table structure and what command you have used to change the address. Any way you can try something like following to rollback to the previous case. If you havent committed the changes nothing to worry. Use rool back command to undo the changes.

update set ='name of location' where ='speed test';


Regards,
Syam

[Ratzie]

Maybe I should be more clear

The speed test was appended to the address, so it is the address with speed test right after it.

[Rajesh_83]

Hi,

here is update query to solve ur problem

UPDATE tablename SET columnname= SUBSTR(columnname,1,INSTR(columnname,'speed test',1,1)-1)

This is will remove the 'speed test' string at the end of ur address part

for eg. if address is like

'some address speed test' this query will update that as 'some address'

[Patti Johnson · Accepted solution]

you can also use.

update table_name set address_column = replace(address_column,'speed test','');

You are replaceing your string with a zero byte character.
This will work regardless of where in the address column your value is.

Patti

[Eric Antunes]

Hi,

Complementing Patty answer, you should see what you are going to update before (I strongly recomend it) doing it:

select address_column, replace( address_column,'speed test',null) address_column_replaced
from table_name
where address_column != replace( address_column,'speed test',null);

And after above query results were verified, do the update:

update table_name
set address_column = replace( address_column,'speed test',null)
where address_column != replace( address_column,'speed test',null);

Best Regards,

Eric Antunes

[Eric Antunes]

Correction:

Complementing Patti's answer...

[Ratzie]

I am trying to get a script to work.
I have the list of tn's that I used when I appended that field but now I can not seem to get it to work.

#!/usr/bin/sh
cat test3|while read i
do echo $i
sqlplus -s admin/admin <update cust_table
set customer = replace (customer,'Speed Trail','');
where tn = ($i);
commit;
quit

EOF
done

[Patti Johnson]

What is the error that you are getting?
Also is tn a number? If not you will need to use quotes around '$i'.

You could use
update cust_table
set customer =
replace (customer,'Speed Trail','')
where customer like '%Speed Trail%'
/

( actually when I cut/pasted your sql I noticed the extra ; at the end of the replace)

You could use
update cust_table
set customer =
replace (customer,'Speed Trail',''); <<---
where customer like '%Speed Trail%'

[Ratzie]

Greatly appreciated!!
Thanks Patty