MySql - 자주 사용하는 문자열 함수 8가지, 예제와 함께 살펴보자

1. TRIM()

String function 중 가장 사용빈도가 높은 함수

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

mysql> select trim(' Seoul, South Korea ');
+------------------------------+
| trim(' Seoul, South Korea ') |
+------------------------------+
| Seoul, South Korea           |
+------------------------------+
1 row in set (0.01 sec)

왼쪽 공백 제거

LTRIM()은 공백만 제거할 수 있으나 leading은 값을 특정할 수 있다.

mysql> select trim(leading '0' from '0000002850');
+-------------------------------------+
| trim(leading '0' from '0000002850') |
+-------------------------------------+
| 2850                                |
+-------------------------------------+
1 row in set (0.00 sec)

오른쪽 공백 제거

RTRIM()은 공백만 제거할 수 있으나 trailing은 값을 특정할 수 있다.

mysql> select trim(trailing ',' from 'col1,col2,col3,');
+-------------------------------------------+
| trim(trailing ',' from 'col1,col2,col3,') |
+-------------------------------------------+
| col1,col2,col3                            |
+-------------------------------------------+
1 row in set (0.00 sec)

2. SUBSTR(), SUBSTRING()

substr, substring은 동일하다.

mysql> select substr('0123456', 2);
+----------------------+
| substr('0123456', 2) |
+----------------------+
| 123456               |
+----------------------+
1 row in set (0.01 sec)

mysql> select substr('0123456', 2, 4);
+-------------------------+
| substr('0123456', 2, 4) |
+-------------------------+
| 1234                    |
+-------------------------+
1 row in set (0.01 sec)

음수를 사용하여 뒤에서 부터 인덱스를 계산한다.

mysql> select substr('0123456', -3);
+-----------------------+
| substr('0123456', -3) |
+-----------------------+
| 456                   |
+-----------------------+
1 row in set (0.01 sec)

mysql> select substr('0123456', -3, 1);
+--------------------------+
| substr('0123456', -3, 1) |
+--------------------------+
| 4                        |
+--------------------------+
1 row in set (0.00 sec)

from을 사용.

mysql> select substr('0123456' from -4 for 2);
+---------------------------------+
| substr('0123456' from -4 for 2) |
+---------------------------------+
| 34                              |
+---------------------------------+
1 row in set (0.01 sec)

mysql> select substr('0123456', -4, 2);
+--------------------------+
| substr('0123456', -4, 2) |
+--------------------------+
| 34                       |
+--------------------------+
1 row in set (0.01 sec)

3. LENGTH()

length는 계산된 바이트를 리턴한다. UTF-8 한글의 경우 한글자당 3바이트를 사용하므로 3으로 계산된다. Character(문자)별 length계산이 필요한 경우 char_length를 사용한다.

mysql> select length('choi');
+----------------+
| length('choi') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> select length('최');
+----------------+
| length('choi') |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

mysql> select char_length('choi');
+----------------+
| length('choi') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

mysql> select char_length('최');
+----------------+
| length('choi') |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

4. LPAD(), RPAD()

mysql> select lpad('2500', 9, '0');
+----------------------+
| lpad('2500', 9, '0') |
+----------------------+
| 000002500            |
+----------------------+
1 row in set (0.00 sec)

mysql> select rpad('123-12-1', 13, '*');
+---------------------------+
| rpad('123-12-1', 13, '*') |
+---------------------------+
| 123-12-1*****             |
+---------------------------+

5. CONCAT()

주어진 문자열을 연결한다. null이 포함된 경우 null을 리턴한다.
number는 varchar로 변환된다.

mysql> select concat('my', 'sql', 8);
+------------------------+
| concat('my', 'sql', 8) |
+------------------------+
| mysql8                 |
+------------------------+
1 row in set (0.01 sec)

6. LOWER(), UPPER()

주어진 영문자를 대소문자로 변환한다.

mysql> select lower('Hello, Wolrd!');
+------------------------+
| lower('Hello, Wolrd!') |
+------------------------+
| hello, wolrd!          |
+------------------------+
1 row in set (0.01 sec)

mysql> select upper('Hello, Wolrd!');
+------------------------+
| upper('Hello, Wolrd!') |
+------------------------+
| HELLO, WOLRD!          |
+------------------------+
1 row in set (0.01 sec)

7. LIKE

Sql조건절의 LIKE구문을 동일하게 Select절에서 사용할 수 있다. 값이 참인 경우 1, 아닌경우 0을 리턴한다.

mysql> select 'MySql8' like 'sql';
+---------------------+
| 'MySql8' like 'sql' |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.01 sec)

mysql> select 'MySql8' like '%sql%';
+-----------------------+
| 'MySql8' like '%sql%' |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.01 sec)

number형에도 사용할 수 있다.

with height_list as(
    select 175 as height union all
    select 183 as height union all
    select 190 as height
)
select height like '18%' from height_list;

0
1
0

반대기능인 NOT LIKE가 있다.

8. REPLACE()

주어진 문자열에서 특정값을 찾아 변환한다.

mysql> select replace('apple apple apple', 'apple', 'banana');
+-------------------------------------------------+
| replace('apple apple apple', 'apple', 'banana') |
+-------------------------------------------------+
| banana banana banana                            |
+-------------------------------------------------+
1 row in set (0.01 sec)

위의 예제에서 처음의 apple만 변경하려는 것처럼 정규식이 필요할 때, REGEX_REPLACE함수를 사용한다.

mysql> select regexp_replace('apple apple apple', '^apple', 'banana');
+---------------------------------------------------------+
| regexp_replace('apple apple apple', '^apple', 'banana') |
+---------------------------------------------------------+
| banana apple apple                                      |
+---------------------------------------------------------+
1 row in set (0.06 sec)

4, 5번째 인자는, 시작부터 몇번째에 매칭된 데이터를 변환할 건지 지정할 수 있다.

mysql> select regexp_replace('a b c d e f', '[a-z]+', 'x', 2, 3);
+----------------------------------------------------+
| regexp_replace('a b c d e f', '[a-z]+', 'x', 2, 3) |
+----------------------------------------------------+
| a b c x e f                                        |
+----------------------------------------------------+
1 row in set (0.01 sec)