MySQL 함수 만들어서 사용하기
CREATE FUNCTION SPLIT_STRING( x text, delim varchar(32), pos int ) RETURNS text RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, '');
CREATE FUNCTION SPLIT_STRING( x text, delim varchar(32), pos int ) RETURNS text RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(x, delim, pos), delim, -1);
SELECT SPLIT_STRING(string, delim, N);
mysql> SELECT SPLIT_STRING('1|2|c|', '|', 1);
+--------------------------------+
| SPLIT_STRING('1|2|c|', '|', 1) |
+--------------------------------+
| 1 |
+--------------------------------+
1 row in set (0.00 sec)
참고하기
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a|bb|ccc|dd', '|', 3), '|', -1);
+------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('a|bb|ccc|dd', '|', 3), '|', -1) |
+------------------------------------------------------------------+
| ccc |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 'a|bb|cc|dd' RLIKE '((\\|).*){3}';
+-----------------------------------+
| 'a|bb|cc|dd' RLIKE '((\\|).*){3}' |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.00 sec)mysql> SELECT 'a|bb|cc|dd' RLIKE '((\\|).*){4}';
+-----------------------------------+
| 'a|bb|cc|dd' RLIKE '((\\|).*){4}' |
+-----------------------------------+
| 0 |
+-----------------------------------+
1 row in set (0.00 sec)
SUBSTRING_INDEX(
str
,delim
,count
)
Returns the substring from string str
before count
occurrences of the
delimiter delim
. If count
is positive, everything to the left of
the final delimiter (counting from the left) is returned. If count
is negative, everything to the right
of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX()
performs a case-sensitive match when
searching for delim
.
mysql>SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql>SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
Thanks to http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/