[MySQL]SUBSTRING()

MySQL 문자열 함수와 연산자

 

SUBSTRING()

   - 문자열 자르기 정도 ?

 

 

SUBSTRING(str, pos)

SUBSTRING(str FROM pos)

SUBSTRING(str, pos, len)

SUBSTRING(str FROM pos FOR len)

의 형태

 

The forms without a  len argument return substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at postion pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string

 

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1. 

 

mysql> SELECT SUBSTRING('Quadratically', 5);
+-------------------------------+
| SUBSTRING('Quadratically', 5) |
+-------------------------------+
| ratically                     |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('foobarbar' FROM 4) ;
+-------------------------------+
| SUBSTRING('foobarbar' FROM 4) |
+-------------------------------+
| barbar                        |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('Quadratically', 5, 6) ;
+----------------------------------+
| SUBSTRING('Quadratically', 5, 6) |
+----------------------------------+
| ratica                           |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('Saklia', -3);
+-------------------------+
| SUBSTRING('Saklia', -3) |
+-------------------------+
| lia                     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('Saklia', 3);
+------------------------+
| SUBSTRING('Saklia', 3) |
+------------------------+
| klia                   |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('Saklia', -5, 3) ;
+----------------------------+
| SUBSTRING('Saklia', -5, 3) |
+----------------------------+
| akl                        |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('Saklia' FROM -4 FOR 2) ;
+-----------------------------------+
| SUBSTRING('Saklia' FROM -4 FOR 2) |
+-----------------------------------+
| kl                                |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> 

 

 

This function is multibyte safe.

 

If len is less than 1, the result is the empty st ring