CODEWITHSUNDEEP
mysqlsql
everbox
everbox

Reputation: 1199

Update column in MySQL

All data in column foo are suffixed by a _. I want to delete this _. Can I do it using SQL rather than writing a script?

Upvotes: 2

Views: 65

Answers (2)

mathematical.coffee
mathematical.coffee

Reputation: 56945

A few ways to do it:

  • TRIM the trailing '_':

    UPDATE mytable
SET foo = TRIM(TRAILING '_' FROM foo)

  • Grab the substring consisting of characters 1 to n-1, n being the length of the string in foo, using SUBSTRING and CHAR_LENGTH:

    UPDATE mytable
SET foo = SUBSTRING(foo,1,CHAR_LENGTH(foo)-1)

I'd recommend the first method as, if you have, say, one row in foo that doesn't end in '_', the first method will leave it as is, whereas the second will take of its last character even though that isn't an underscore.

Upvotes: 4

Jordan Running
Jordan Running

Reputation: 106137

Sure:

SELECT TRIM( TRAILING '_' FROM foo ) AS foo FROM ... ;

(Documentation)

Upvotes: 0

Related Questions