CODEWITHSUNDEEP
phpmysqlsqldatabasedatabase-table
Sabha
Sabha

Reputation: 619

Update part of a value in mysql table

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...

Upvotes: 0

Views: 881

Answers (5)

dev777
dev777

Reputation: 3

Please run below query:

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

Upvotes: 0

Ronser
Ronser

Reputation: 1875

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);
    } 
}

Upvotes: 1

jmsds
jmsds

Reputation: 225

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;

Upvotes: 1

user2085632
user2085632

Reputation: 15

how About with query Replace 

 Update user 
set column = REPLACE(column, '*','')

Upvotes: 1

jjchr
jjchr

Reputation: 105

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.

Upvotes: 2

Related Questions