0

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 '-'.

  • If you post the table schema and a couple example rows, that'd be an easy fix to write as a straightforward UPDATE query. – Lance Jun 29 '18 at 1:56
  • @Dtorr1981 I fully agree with Lance. Mock up a minimal, yet accurate, SQLFiddle for us to play with and we can show you exactly what to do. Effectively we are only asking you to go into your phpMyAdmin and export your table structure, then dump the structure and 10-20 differing rows of data into the left-side input box, click Build Schema, then offer the new url to us. Then explain your EXACT desired output for each row. Not only will these details help you to receive faster, higher quality answers, it will increase the likelihood that your question will help others and earn upvotes. – mickmackusa Jun 29 '18 at 4:27
  • Hi Guys, I cannot seem to build an SQLfiddle as the content is too large > 8000 char even with only 5 lines of data. I have edited my question to include a sample of the SQL data. – Dtorr1981 Jul 2 '18 at 10:09
  • @Dtorr1981 I can't think of any clean/brief ways to do this within a query. Is using php out of the question? Do you have a finite list of possible non-alphabetical characters? Might there be numeric values as well? – mickmackusa Jul 12 '18 at 11:40
  • Are there apostrophes or hyphens? How does that impact your desired result? – mickmackusa Jul 12 '18 at 11:48
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.

3
+25

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 = '';
  • Thank you, this seems to be almost there however the string is still including special characters such as '&' and ',' - is there a way to exclude these? I also need to add a WHERE statement to only update those with empty aliases. This shouldn't be an issue for me to add myself though. – Dtorr1981 Jul 12 '18 at 7:44
  • @YellowWebMonkey this doesn't seem to deal with 0 through 9 or apostrophes, and more. I think pure sql is a poorly suited choice for this task. – mickmackusa Jul 12 '18 at 11:46
  • @mickmackusa, the numbers should be fine in alias. As far as special characters, the cleaner way would be do a REGEXP_REPLACE, I just didn't have time to play with it. Dtorr1981, if you give a full list of special characters you want to account for, I can edit the answer when I get a chance. – YellowWebMonkey Jul 12 '18 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.