A slug is a short name using human-readable keywords to identify a web page. For example, in the following URL
http://www.example.com/schools/play-school
the slug is play-school. This post shows how to automatically generate slug using SQL in MySQL.
Suppose we have a table schools a field name, and we wish to generate a slug for each school using
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
WITH RECURSIVE children AS ( SELECT '0'::varchar AS parent_code, A.level_code, A.level_name, A.code_length, 1 AS DEPTH FROM c_levels A WHERE A.level_code ='05' UNION ALL SELECT B.parent_code AS parent_code, B.level_code, B.level_name, B.code_length, DEPTH + 1 FROM c_levels B JOIN children C ON ( B.level_code = C.parent_code )) SELECT * FROM children ORDER BY DEPTH DESC |
|
1 2 3 4 5 6 |
UPDATE m_lmr_period SET lmr_status = CASE WHEN lmr_status IN ('P') THEN 'O' WHEN lmr_status IN ('C') THEN 'P' ELSE 'O' END |
|
1 |
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50); |
|
1 |
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) DEFAULT 'whatever' NOT NULL; |
|
1 2 3 4 5 6 7 |
UPDATE dummy SET customer=subquery.customer, address=subquery.address, partn=subquery.partn FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ ...) AS subquery WHERE dummy.address_id=subquery.address_id; |