Removing a word from data in a column

newbie2004 · #1 (**permalink**) 09-13-04, 16:09

Ok, I have a table that has a column called email.

Most of the email address have newbie@yahoo.com, however, some email addresses have mailto:newbie@yahoo.com.

How do I get rid of the mailto:?

shoblock · #2 (**permalink**) 09-13-04, 16:18

use ltrim
ltrim(col, 'mailto:')

newbie2004 · #3 (**permalink**) 09-14-04, 10:29

Thank you shoblock.

I tried to use ltrim and I am getting no results but I know there is like 10,000 rows that needs to be fixed.

Here is my sql:

delete from unsub_final4
WHERE ltrim(email, 'mailto:')like 'mailto:%';

gbrabham · #4 (**permalink**) 09-14-04, 10:32

Use INSTR function

substr(col,instr(col,'mailto:',1)-1,numberofpositions)

HTH
Gregg

ss659 · #5 (**permalink**) 09-14-04, 10:39

Are you trying to delete those rows, or do you simply want to take away the mailto: part?

Because if you want to delete anything with a mailto, you dont need a trim or susbtr function - you would just do this:

Code:

delete from unsub_final4
where email like 'mailto:%'

BUT if you want to just remove the mailto: part and not delete the record, you need to use an update statement, and either the substr or ltrim (or replace or translate) functions.

Just wanted to clarify, because your delete statement is overkill right there.

newbie2004 · #6 (**permalink**) 09-14-04, 10:46

Thank you for your replies.

u are correct I just want to remove the mailto from the row.

So how about this:

update unsub_final4
set email = (select email from unsub_final4 WHERE ltrim(email, 'mailto:')like 'mailto:%');

beilstwh · #7 (**permalink**) 09-14-04, 10:48

update unsub_final4
set email = replace(email,'mailto:',null);

newbie2004 · #8 (**permalink**) 09-14-04, 11:02

Thank you all!!

You are teaching me so much!!!

WilliamR · #9 (**permalink**) 09-14-04, 14:06

LTRIM is not so useful here.

Code:

WITH testit AS
(
  SELECT 'mailto:tom@somewhere.net' AS col FROM dual
  UNION ALL
  SELECT 'tom@somewhere.net'       FROM dual
  UNION ALL
  SELECT 'tammi@somewhere.net'     FROM dual
  UNION ALL
  SELECT 'lamatamer@somewhere.net' FROM dual
)
SELECT col
     , LTRIM(col, 'mailto:') AS ltrimmed
FROM   testit;

COL                       LTRIMMED
------------------------- -------------------------
mailto:tom@somewhere.net  @somewhere.net
tom@somewhere.net         @somewhere.net
tammi@somewhere.net       @somewhere.net
lamatamer@somewhere.net   er@somewhere.net

4 rows selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech