Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    21

    Unanswered: Delete partial from column

    Guys, i have a table that one of the columns (Email To) is
    a concatenated list of email addresses separated by semi colons ";".

    i.e.:

    rrb7@yahoo.com;richard.butcher@sthou...ator@sthou.com

    etc like that.
    each row varies with one exception. administrator@sthou.com is in each one.

    is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
    and then looping thru and updating each row?

    thanks again for any easy answer
    rik

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You should be able to use the Replace function to delete that with an update query. It's available in both Access and T_SQL.
    Paul

  3. #3
    Join Date
    Dec 2003
    Posts
    39
    Jelly Link update the post : It should be UPDATE, not DELETE
    UPDATE <table_name>
    set EmailTo = replace(EmailTo, 'administrator@sthou.com;','')
    where EmailTo like 'administrator@sthou.com;%'

    UPDATE <table_name>
    set EmailTo = replace(EmailTo, ';administrator@sthou.com;',';')
    where EmailTo like '%;administrator@sthou.com;%'

    UPDATE <table_name>
    set EmailTo = replace(EmailTo, ';administrator@sthou.com','')
    where EmailTo like '%;administrator@sthou.com'
    Last edited by Jelly Link; 02-01-06 at 04:24.
    Link Link

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by Jelly Link
    delete <table_name>
    delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.
    Paul

  5. #5
    Join Date
    Dec 2003
    Posts
    39
    ups sorry......its UPDATE update....set.......where............

    ehm....i use the where clause to remove the separator ";" and the email address when administrator@sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@sthou.com (eg : blablaadministrator@sthou.com)
    Last edited by Jelly Link; 02-01-06 at 04:35.
    Link Link

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by pbaldy
    delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.
    Quote Originally Posted by jelly link
    ups sorry......its UPDATE update....set.......where............

    ehm....i use the where clause to remove the separator ";" and the email address when administrator@sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@sthou.com (eg : blablaadministrator@sthou.com)
    Delete instead of update!!!!!!!!!.Stick this post and dont allow the poster to edit again in this post.hehehehe.lets every one see.I can see the panic in jelly link's face that time ,lol
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  7. #7
    Join Date
    Dec 2003
    Posts
    39
    sorry......hav so many things in my head
    Link Link

  8. #8
    Join Date
    Jan 2006
    Posts
    21
    it works great - i truly appreciate the help on this. Working with Oracle for so long, i feel like im running to catch up. it all looks familiar, but really not at all
    thanks again
    rik

Posting Permissions

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