Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DELIMITER ;;
- DROP FUNCTION IF EXISTS `slugify`;;
- CREATE FUNCTION `slugify`(dirty_string varchar(255)) RETURNS varchar(255) CHARSET utf8
- DETERMINISTIC
- BEGIN
- DECLARE x, y , z , k INT;
- DECLARE temp_string, new_string, accents, noAccents VARCHAR(255);
- DECLARE is_allowed BOOL;
- DECLARE c, check_char VARCHAR(1);
- -- IF NULL DO NOT PROCEED
- If dirty_string IS NULL Then
- return dirty_string;
- End If;
- set temp_string = LOWER(dirty_string);
- -- REPLACE ACCENTS
- -- WITH CAPS
- -- set accents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
- -- set noAccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
- -- ONLY SMALL CAPS
- set accents = 'ýỳỷỹỵáàảãạâấầẩẫậăắằẳẵặéèẻẽẹêếềểễệúùủũụưứừửữựíìỉĩịóòỏõọơớờởỡợôốồổỗộđ·/_,:;';
- set noAccents = 'yyyyyaaaaaaaaaaaaaaaaaeeeeeeeeeeeuuuuuuuuuuuiiiiioooooooooooooooood------';
- set k = CHAR_LENGTH(accents);
- while k > 0 do
- set temp_string = REPLACE(temp_string, SUBSTRING(accents, k, 1), SUBSTRING(noAccents, k, 1));
- set k = k - 1;
- end while;
- -- CONVERT & TO EMPTY SPACE
- Set temp_string = REPLACE(temp_string, '&', '');
- -- REPLACE ALL UNWANTED CHARS
- Select temp_string REGEXP('[^a-z0-9\-]+') into x;
- If x = 1 then
- set z = 1;
- set k = CHAR_LENGTH(temp_string);
- While z <= k Do
- Set c = SUBSTRING(temp_string, z, 1);
- Set is_allowed = FALSE;
- If !((ascii(c) = 45) or (ascii(c) >= 48 and ascii(c) <= 57) or (ascii(c) >= 97 and ascii(c) <= 122)) Then
- Set temp_string = REPLACE(temp_string, c, '-');
- End If;
- set z = z + 1;
- End While;
- End If;
- Select temp_string REGEXP("^-|-$|'") into x;
- If x = 1 Then
- Set temp_string = Replace(temp_string, "'", '');
- Set z = CHAR_LENGTH(temp_string);
- Set y = CHAR_LENGTH(temp_string);
- Dash_check: While z > 1 Do
- If STRCMP(SUBSTRING(temp_string, -1, 1), '-') = 0 Then
- Set temp_string = SUBSTRING(temp_string,1, y-1);
- Set y = y - 1;
- Else
- Leave Dash_check;
- End If;
- Set z = z - 1;
- End While;
- End If;
- Repeat
- Select temp_string REGEXP("--") into x;
- If x = 1 Then
- Set temp_string = REPLACE(temp_string, "--", "-");
- End If;
- Until x <> 1 End Repeat;
- If LOCATE('-', temp_string) = 1 Then
- Set temp_string = SUBSTRING(temp_string, 2);
- End If;
- Return temp_string;
- END;;
- DELIMITER ;
- SELECT DISTINCT
- nd.news_id AS id,
- unix_timestamp(date_added) AS createdAt,
- unix_timestamp(date_modified) AS updatedAt,
- name AS title,
- slugify(name) AS slug,
- image AS thumbUrl,
- short_description AS description,
- description AS content,
- viewed AS viewCount,
- status as isActive,
- news_category_id as category
- FROM oc_news n
- LEFT JOIN oc_news_description nd ON (n.news_id = nd.news_id)
- LEFT JOIN oc_news_to_category n2c ON (n.news_id = n2c.news_id)
Add Comment
Please, Sign In to add comment