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/