0

I have a table "users" in mysql with around 2K records. In the table, there is a field "status". The status can be of 2 to 3 words. In few status, at the end, there is a space followed by an asterisk mark. I want to remove the space and asterisk. Please help me with a query to update the values. Here are some examples

Not smiling
always laughing *
never jumps *
keep it up
oh my god *

I want to change this to:

Not smiling
always laughing
never jumps
keep it up
oh my god

SELECT * FROM users WHERE ?????? (How to write the condition?)

EDIT:
Thanks everyone for the answers. Can someone shed some light on which answer is the best considering replacing 2000+ records, utilization of computer resources, memory, efficiency etc...

Sabha
  • 621
  • 10
  • 32

5 Answers5

2

you could use following syntax/command to search and replace *

update users set status = replace(status, ' *', '') where instr(status, ' *') > 0;

This will find the string with * and replace without *.

Hope it helps.

jjchr
  • 105
  • 3
1

how About with query Replace 

 Update user 
set column = REPLACE(column, '*','')
user2085632
  • 15
  • 1
  • 7
1

try this...

$sql='SELECT id,status FROM users WHERE status like %* %';

while( $rs=$conn->query($sql) )
{
   $newStatus = str_replace(' *',rs[1]);
    $sqlUpdate="UPDATE users SET status =$newStatus WHERE id=rs[0]";

    if($conn->query($sqlUpdate) === false) {
        trigger_error('Wrong SQL: ' . $sqlUpdate. ' Error: ' . $conn->error, E_USER_ERROR);
    } 
}
Ronser
  • 1,855
  • 6
  • 20
  • 38
1

Try this single line query to find and replace only the records with * in status

update users set status = replace(status, ' *', '') where instr(status, ' *') > 0;
jmsds
  • 225
  • 1
  • 7
0

Please run below query:

update users set status=RTRIM(SUBSTRING_INDEX(status, '', 1)) where status like '%'

dev777
  • 3
  • 1