How to generate missing column data using a reformatted substring of another column?

Question

Due to a process issue (i.e. client not following instructions) I have over 900 urls with missing aliases in a mosets tree table.

If possible I would like to create an SQL query that will generate an alias from the link_title by selecting, say, the first 3-5 words and obviously removing any numbers and special characters, replacing spaces with a - and making all characters lowercase.

My MySQL skills really aren't great, but so far I have created a select query for testing:

SELECT SUBSTRING_INDEX(`link_name`, ' ', 5) AS linkalias FROM `m5cob_mt_links`

Of course, I'd need to convert the finalised query into an update of some description. Could anyone provide any advice, or let me know if there is an easier way to populate all of the missing aliases?

Relevant portion of the SQL schema and data:

CREATE TABLE `m5cob_mt_links` (
  `link_id` int(11) NOT NULL,
  `link_name` varchar(255) NOT NULL,
  `alias` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `m5cob_mt_links` (`link_id`, `link_name`, `alias`) VALUES
(6, 'Convivio Bar & Restaurant Chester Cheshire UK', ''),
(7, 'Slug and Lettuce Chester Cheshire UK', ''),
(8, 'Da Rosa Restaurant & Wine Bar Torrevieja Costa Blanca Spain', ''),
(9, 'Bar Lounge Chester Cheshire UK', ''),
(10, 'Telfords Warehouse Chester Cheshire UK', ''),
(11, 'The Botanist Chester Cheshire UK', ''),
(12, 'Missoula Chester Cheshire UK', ''),
(13, 'Fiesta Havana Chester Cheshire UK', ''),
(14, 'El Oceano Hotel Restaurant, Torrenueva, Marbella, Malaga', ''),
(15, 'La Plaza Restaurant Riviera del Sol Mijas Costa Malaga Spain', '');

So as an example, the first row of data has a link_name of Convivio Bar & Restaurant Chester Cheshire UK. With the query, I would like to populate the alias as convivio-bar-restaurant-chester-cheshire thus taking the first 5 words, removing the capitalisation and punctuation and replacing the spaces with '-'.

Answer 1

If you are running MySQL 8.0+, you have access to the powerful and task-appropriate REGEXP_REPLACE() function.

The bad news is REGEXP_REPLACE() is not suitable for use in a single query solution until you have access to the not-yet-released MySQL 8.0.12 that fixes a bug with the function. This is what happens with the sub-8.0.12 bug when updating multiple rows at once.

From MySQL 8.0.12+, here is my suggested query:

UPDATE `m5cob_mt_links` 
SET `alias` = SUBSTRING_INDEX(REGEXP_REPLACE(REGEXP_REPLACE(LOWER(`link_name`), '[^a-z ]+', ''), ' +', '-'), '-', 5)
WHERE `alias` = '';

This forces all characters to be lowercase, eliminates unwanted characters, then replaces one or more spaces with a single hyphen, then truncates the string to a maximum of 5 "words".

If you are willing to the query n times -- each time with LIMIT 1 appended to each query -- you can avoid the buggy outcome. Here is a demo. So if this were my project, I'd go into phpMyAdmin or WorkBench, generate ~50 copies of the query, and execute the batches of queries until there were no more affected rows. This will deliver the most accurate and reliable results on your ~900 rows.

---

If you are inclined to use a PHP workaround, this will have the same effect:

Code: (See the results with modified fringe cases in this online demo)

// SELECT `link_id`, `link_name`, `alias` FROM `m5cob_mt_links` WHERE `alias` = ''
// indexed resultset...
$resultset = [
    [6, 'Convivio Bar & Restaurant Chester Cheshire UK', ''],
    [7, 'Slug and Lettuce Chester Cheshire UK', ''],
    [8, 'Da Rosa Restaurant & Wine Bar Torrevieja Costa Blanca Spain', ''],
    [9, 'Bar Lounge Chester Cheshire UK', ''],
    [10, 'Telfords Warehouse Chester Cheshire UK', ''],
    [11, 'The Botanist Chester Cheshire UK', ''],
    [12, 'Missoula Chester Cheshire UK', ''],
    [13, 'Fiesta Havana Chester Cheshire UK', ''],
    [14, 'El Oceano Hotel Restaurant, Torrenueva, Marbella, Malaga', ''],
    [15, 'La Plaza Restaurant Riviera del Sol Mijas Costa Malaga Spain', '']
];

foreach ($resultset as $row) {
    $purged = preg_replace('~[^a-z ]+~', '', strtolower($row[1]));
    $split = preg_split('~ +~', $purged, 6, PREG_SPLIT_NO_EMPTY);
    $new_alias = implode('-', array_slice($split, 0, 5));
    echo "{$row[0]}: $new_alias\t\t(from: {$row[1]}\n";

    // UPDATE `m5cob_mt_links` SET `alias` = '$new_alias' WHERE `link_id` = $row[0]
}

... yes, this too means ~900 UPDATE calls, but if I understand correctly, this is a one-off table repair job.

---

To offer a pure sub-MySQL8.0 query in a single call to achieve the desired result ONLY on the out-of-range characters from your provided sample input (&, ,), you could use:

(Fiddle Demo)

UPDATE m5cob_mt_links
SET link_alias = SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(link_name), '&', ''), ',', ''), ' ', '-'), '--', '-'), '-', 5)
WHERE link_alias = '';

The bad news here is that it is very difficult to read/maintain and is incredibly inflexible because it requires an additional REPLACE() call for every new replacement character. This query will not handle the numbers and additional symbols that you mention in your question -- not out of the box anyhow. Depending on your real data, you may need to run REPLACE(val, '--', '-') twice to mop up lingering consecutive dashes. It seems an unfortunately tedious task to have to review your query's success on the ~900 rows.

*note that in both of my query-based solutions, SUBSTRING_INDEX() is called last. This ensures that you receive the maximum number of words in your 5-word grab.

Answer 2

I believe this should do it:

UPDATE m5cob_mt_links 
SET alias = REPLACE(SUBSTRING_INDEX(LOWER(link_name), ' ', 5),' ','-');

EDIT: Added REPLACE for ampersand, comma, and double dashes as well as a conditional for blank alias. There is probably a more succinct way of doing the REPLACEs, but this is a workable solution for now.

UPDATE m5cob_mt_links 
SET alias = REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(LOWER(link_name), ' ', 5),' ','-'),'&',''),",",""),'--','-')
WHERE alias = '';