topic Re: remove word from column in Operating System - HP-UX

remove word from column

Ratzie — Thu, 20 Oct 2005 15:11:46 GMT

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.

Re: remove word from column

Rodney Hills — Thu, 20 Oct 2005 15:18:08 GMT

With perl you can do-
perl -p -e 's/$?speed test$?//' outfile

HTH

-- Rod Hills

Re: remove word from column

Ranjith_5 — Thu, 20 Oct 2005 15:28:38 GMT

Hi,

Can be done with SED as well.

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

Regards,
Syam

Re: remove word from column

Ratzie — Thu, 20 Oct 2005 15:33:19 GMT

I need to do this in oracle

Re: remove word from column

Ranjith_5 — Thu, 20 Oct 2005 15:58:37 GMT

you mean oracle table?

Regards,
Syam

Re: remove word from column

Ranjith_5 — Thu, 20 Oct 2005 16:16:27 GMT

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

Regards,
Syam

Re: remove word from column

Ranjith_5 — Thu, 20 Oct 2005 16:22:47 GMT

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

Re: remove word from column

Ratzie — Thu, 20 Oct 2005 18:20:03 GMT

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.

Re: remove word from column

Rajesh_83 — Fri, 21 Oct 2005 00:18:24 GMT

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'

Re: remove word from column

Patti Johnson — Fri, 21 Oct 2005 10:44:19 GMT

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

Re: remove word from column

Eric Antunes — Fri, 21 Oct 2005 11:05:11 GMT

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

Re: remove word from column

Eric Antunes — Fri, 21 Oct 2005 11:06:04 GMT

Correction:

Complementing Patti's answer...

Re: remove word from column

Ratzie — Fri, 21 Oct 2005 11:54:19 GMT

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

Re: remove word from column

Patti Johnson — Fri, 21 Oct 2005 12:17:28 GMT

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%'

Re: remove word from column

Ratzie — Fri, 21 Oct 2005 16:29:10 GMT

Greatly appreciated!!
Thanks Patty