#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0

    What symbol to use instead?


    Hello Guys,
    I hope I can make sense and get an answer..
    I'm trying to update a field on a table..
    This field contains different forwarding methods for a message.
    For Example: EIioT which stands for Email Immediately incoming outgoing T (not sure)
    If I want to change for only incoming I can change it to: EIiT but this way, if the first letter was S (for SMS) will default all to E (email).
    How can I just modify/add/remove a letter in the middle without effecting others?
    I tried * and ?..

    Hope yopu can help..

    Regards to all
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    String Replace():
    Code:
    UPDATE ...
    SET
      yourColumn = REPLACE(yourColumn, 'x', 'y')
    WHERE
      yourColumn LIKE '%x%'
    The WHERE LIKE is to explicitly choose the rows that need to be updated.
    /Stefan
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0
    Thank you Stephan..

    can't get it though..

    I used:

    Set FORWARD_DETAILS = '%IioT'

    out of all the above letters I want only the first not to be touched...

    do I need the % sign at the end too?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0
    This is what I wrote:

    update MESSAGE_ACTIONS
    SET
    FORWARD_DETAILS = REPLACE ( FORWARD_DETAILS ' i ' , ' io ' )
    WHERE
    FORWARD_DETAILS LIKE ' %i% '

    not working
  8. #5
  9. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    And what is the error message that you get?

    Could it be:
    Code:
    mysql> update MESSAGE_ACTIONS
        -> SET
        -> FORWARD_DETAILS = REPLACE ( FORWARD_DETAILS ' i ' , ' io ' )
        -> WHERE
        -> FORWARD_DETAILS LIKE ' %i% ';
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL serv
    er version for the right syntax to use near '' i ' , ' io ' )
    WHERE
    FORWARD_DETAILS LIKE ' %i% '' at line 3
    And by looking and stepping through this code I can see that:
    1. You don't have a comma between the column name FORWARD_DETAILS and the 'i'.
    2. You have spaces between the ' sign and the letter i and the letters io, which means that mysql is right now trying to replace the string '(space)i(space)' with the string '(space)io(space).
    3. You have spaces inside the string in LIKE also.

    So try this instead:
    Code:
    update MESSAGE_ACTIONS
    SET
    FORWARD_DETAILS = REPLACE ( FORWARD_DETAILS, 'i' , 'io' )
    WHERE
    FORWARD_DETAILS LIKE '%i%'
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo