CODEWITHSUNDEEP
mysqlsql
Zoha Ali Khan
Zoha Ali Khan

Reputation: 1699

Remove some text from a column values

I have a column in my table named title_id with some title ids. Each title id is stored like 

title_1 
title_2
title_3

where 1, 2, 3 are the actual ids of the titles. I want to remove the " title_ " text from all the records. I have around 5000 records so I can't edit them manually.

How can I do it with a query.

Thanks in advance

Upvotes: 0

Views: 3191

Answers (4)

migu
migu

Reputation: 1401

Try something like

update table_name set id=substring(id, from length('title_'))
where id like 'title%';

I didn't test this, because I have no MySQL DB available here. The syntax for the substring function is from the MySQL docs.

Upvotes: 1

Josien
Josien

Reputation: 13877

Check out the REPLACE function. You can do something like:

UPDATE table
  SET title_id = REPLACE(title_id, 'title_', '');

(Ah, and be sure to first test your UPDATE query by running a SELECT query!)

Upvotes: 1

WojtekT
WojtekT

Reputation: 4775

UPDATE table_name SET title_id = REPLACE(title_id, 'title_','')

Upvotes: 1

Miqdad Ali
Miqdad Ali

Reputation: 6147

Update table_name set `title_id` = REPLACE(`title_id`,'title_','');

I didnt' tested it . Please check

Upvotes: 3

Related Questions