1748033 Members
5033 Online
108757 Solutions
New Discussion юеВ

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 14
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
New Member

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