cancel
Showing results for 
Search instead for 
Did you mean: 

remove word from column

SOLVED
Go to solution
Ratzie
Super Advisor

remove word from column

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.
14 REPLIES
Rodney Hills
Honored Contributor

Re: remove word from column

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

HTH

-- Rod Hills
There be dragons...
Ranjith_5
Honored Contributor

Re: remove word from column

Hi,

Can be done with SED as well.

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

Regards,
Syam
Ratzie
Super Advisor

Re: remove word from column

I need to do this in oracle
Ranjith_5
Honored Contributor

Re: remove word from column

you mean oracle table?

Regards,
Syam
Ranjith_5
Honored Contributor

Re: remove word from column

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

Regards,
Syam
Ranjith_5
Honored Contributor

Re: remove word from column

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
Super Advisor

Re: remove word from column

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
Occasional Visitor

Re: remove word from column

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
Respected Contributor
Solution

Re: remove word from column

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
Honored Contributor

Re: remove word from column

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

Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: remove word from column

Correction:

Complementing Patti's answer...
Each and every day is a good day to learn.
Ratzie
Super Advisor

Re: remove word from column

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
Respected Contributor

Re: remove word from column

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
Super Advisor

Re: remove word from column

Greatly appreciated!!
Thanks Patty