mysql>
mysql> delimiter $$
mysql>
mysql>
mysql> CREATE FUNCTION shorten(s VARCHAR(255), n INT)
-> RETURNS VARCHAR(255)
-> BEGIN
-> IF ISNULL(s) THEN
-> RETURN '';
-> ELSEIF n<15 THEN
-> RETURN LEFT(s, n);
-> ELSE IF CHAR_LENGTH(s) <= n THEN
-> RETURN s;
-> ELSE
-> RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
-> END IF;
-> END IF;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT shorten("abcdefghijklmnopqrstuvwxyz", 15)$$
+-------------------------------------------+
| shorten("abcdefghijklmnopqrstuvwxyz", 15) |
+-------------------------------------------+
| abcde ... vwxyz |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> SHOW CREATE FUNCTION shorten;
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---
-------------------+----------------------+--------------------+
| Function | sql_mode | Create Function | ch
aracter_set_client | collation_connection | Database Collation |
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---
-------------------+----------------------+--------------------+
| shorten | | CREATE DEFINER=`root`@`localhost` FUNCTION `shorten`(s VARCHAR(255), n INT) RETURNS varchar(255) CHARSET latin1
BEGIN
IF ISNULL(s) THEN
RETURN '';
ELSEIF n<15 THEN
RETURN LEFT(s, n);
ELSE IF CHAR_LENGTH(s) <= n THEN
RETURN s;
ELSE
RETURN CONCAT(LEFT(s, n-10), ' ... ', RIGHT(s, 5));
END IF;
END IF;
END | latin1 | latin1_swedish_ci | latin1_swedish_ci |
+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---
-------------------+----------------------+--------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop function shorten;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>