How to generate Alias using sql query

Question

I want to convert all my title names to a url alias using SQL query . currently im using sql REPLACE for replace "" (space) with "-" .

But i want to avoid all speacial charactors and spaces between words and convert the title names to aliases

Ex: A B & sons should be a-b-sons

This is my current query

 UPDATE content SET alias = title,alias = LOWER(REPLACE(alias," ", "-"));

is there any option where i can add more speicifed for abocve query for get titles like above with special characters

Ex: A B & sons should be a-b-sons

Please advice

Is this [tag:mysql] or [tag:sql-server]? Surely it cannot be both... — Mureinik, Jul 23 '14 at 17:30
FYI, that's often called "slug", "semantic URL" or "friendly URL". — Álvaro González, Jul 23 '14 at 17:32
possibly use regular expressions to strip all but numbers and letters and - after replacing space... RDBMS matters though for how and what functions are available. — xQbert, Jul 23 '14 at 17:32
http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Michael McGriff, Jul 23 '14 at 17:38

score 0 · Answer 1 · answered Jul 23 '14 at 18:16

0

You can use REPLACE() function multiple time like

UPDATE content 
SET alias = LOWER(REPLACE(REPLACE(alias," ", "-"),"&","-"))
WHERE alias='A B & sons';

answered Jul 23 '14 at 18:16

Rahul

will it give outcome with multiple hiphens (-) between words. is it possible to remove "&" using replace wthout adding "-". Pls advice – user3786234 Jul 23 '14 at 18:58
Per your post: if alias value is `A B & sons` then the query will result in `a-b-sons`. Try it and see for yourself. Yes, you can replace without adding "-" but you will have provide a replacing character. – Rahul Jul 23 '14 at 19:04
@user3786234, consider accepting the answer if it helped by clicking the check button under voting button. – Rahul Jul 24 '14 at 19:48

1 Answers1