MYSQL Replace Statement using _ (UnderScore) or %

user_12900270 0
Viewed 72

I need to Update a Column using Replace Query but I won't know 10 characters and i need to fill that up with _ just like in LIKE statements

UPDATE USERS SET LASTONLINE = REPLACE(LASTONLINE, "User1:__________", "User1:1620647000") WHERE NUMBER = '9988776655'

I'm trying to store timestamp of when the user was last online. I cannot change the table structure for many reasons which makes the question bigger. I just want to replace the old Timestamp which i won't know with the new one. I'm sure this can be done but having trouble with the logic. Any help is appreciated

Structure

NUMBER | LASTONLINE
9988.. | User1:<timestamp>,User2:<timestamp>
amazon-web-services sql-update mysql sql string
2 Answers

If you want to set a timestamp value, set it to a timestamp value. For instance:

UPDATE USERS
    SET LASTONLINE = NOW()
    WHERE NUMBER = '9988776655';

I don't know what timestamp value you want to set it to. This just uses the current time.

I have no idea why you are trying to set a timestamp to a string, but that won't do anything useful.

user_11440350

Assuming your LASTONLINE field contains a string of number of users1,2,3,4... and from your example query you would know which user to update as well as the number being updated to, you can use subtring get the substring of string before the User (User1 in your case in your example), concatenate with desired string, and get the substring from the position of User1 plus 16 spaces which means string after the "User1: plus 10 digits").

  update USERS
        set LASTONLINE = concat(substring_index(LASTONLINE , 'User1:', 1),
                             'User1:1620647000',
                              substr(LASTONLINE,LOCATE('User1:',LASTONLINE)+16))
        where NUMBER = '9988776655';
user_59210490
Related