Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: Removing a word from data in a column

    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:?

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    use ltrim
    ltrim(col, 'mailto:')
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Apr 2004
    Posts
    113
    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:%';

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Use INSTR function

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

    HTH
    Gregg

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    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.
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Apr 2004
    Posts
    113
    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:%');

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    update unsub_final4
    set email = replace(email,'mailto:',null);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Apr 2004
    Posts
    113
    Thank you all!!

    You are teaching me so much!!!

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •