Update alias column base on name column

186
November 29, 2017, at 00:56 AM

Is it possible to update an "alias" column based on a "name" column on a MySQL database? The alias column is now empty and I need to populate it this way:

Name                                Alias
United Nations                      united-nations
World Health Organization           world-health-organization
Ministre des Affaires étrangères    ministre-des-affaires-etrangeres
Gobierno de España                  gobierno-de-espana

The this is to change all to lowercase, remove all special characters (like ñ, ó, ö etc...) and change spaces to hyphens. Is it possible?

Thank you for the help in advance

Answer 1

It looks like replace() and lower() do what you want:

update t
  set alias = replace(lower(name), ' ', '-');

You appear to also want to change the accented characters to ASCII, so:

update t
  set alias = convert(replace(lower(name), ' ', '-') using latin1);
READ ALSO
How to use “ WITH ” clause in MySQL trigger?

How to use “ WITH ” clause in MySQL trigger?

I am trying to write a Mysql queryI am getting some error on WITH clause of select function

198
Get top 5 records for user, average records and rank by averages

Get top 5 records for user, average records and rank by averages

I have a table for user records that hold scores (and need to get only users that are members)I need to get the top 5 scores for each user during this year, average the scores and return the top 10 users

135
How can I set innodb_file_format flag in Google Cloud SQL

How can I set innodb_file_format flag in Google Cloud SQL

So I'm creating a table in Google Cloud SQL having 272 columns in itBut at the time of table creation, I'm getting following error

285
MySQL is Active but Can't find '/var/run/mysqld/mysqld.sock'

MySQL is Active but Can't find '/var/run/mysqld/mysqld.sock'

I know this is a popular question, but I tried many solution and offer more detail informationHope some experienced people can save my life

249