Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2010

    Unanswered: Replacing text in a column for all rows in the table


    I have a column like this:

    Table: User
    name, field1
    Jack 1000|1001|1003
    Berg 2000|1001|2004
    Paul 1001|1000
    Jane 1001

    Now, I would like to replace all "1001" with nothing, and also remove the "|"-separator behind 1001 if it exists, basically removing both "1001" and "1001|", so the resulting table looks like this:

    name, field1
    Jack 1000|1003
    Berg 2000|2004
    Paul 1000

    My tries, been plenty, but here are some:
    UPDATE UserTable
    SET field1 = REPLACE(field1, '1001', '')
    UPDATE UserTable
    SET field1 = REPLACE(field1, '1001|', '')
    But the above queries replaces field1 only if the whole field matches '1001' or '1001|'...


    Wow, my mistake, the above queries do work, just like I want them to. Thanks, solved.

    Edit: MCrowley, yes I know, I just happened to write them in this order on this post... did not do a copy of the actual query.
    Last edited by ManyTimes; 10-25-12 at 03:25.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    The only thing I might add is to reverse the order of the two queries. The first one would leave a bunch of "|"s around in the data that the second one would not be able to clean out.

Tags for this Thread

Posting Permissions

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