CODEWITHSUNDEEP
mysqlsql
Josip Gvozdić
Josip Gvozdić

Reputation: 147

Delete specific word from table column in MySQL?

I have ONE column in MySQl table which contains words in this format:

Name - Name2 - Name3

and I'd like to delete Name2, and to leave just Name - Name3

So I just need to delete middle word, and not the entire column! The middle word is always the same.

Is that possible?

Upvotes: 0

Views: 2805

Answers (2)

Sayed Newaz
Sayed Newaz

Reputation: 113

The easiest way is to use REPLACE.

UPDATE table SET column=REPLACE(column,'Yourstring','')

Suppose I have a table name car, and inside this table, there is a column called version. In this version, there are few data which has the string 'Auto', and I want to remove this.

GT 1.4 Turbo MultiAir 2d Auto -> GT 1.4 Turbo MultiAir 2d

So, the simple solution will be:

UPDATE cars SET version=REPLACE(version,'Auto','')

It will work for every occurrence.

Upvotes: 1

juergen d
juergen d

Reputation: 204746

update your_table
set your_column = concat(SUBSTRING_INDEX(your_column, '-', 1), '-',
                         SUBSTRING_INDEX(your_column, '-', -1))

SQLFiddle example
substring-index doc

Upvotes: 5

Related Questions