개발노트

[SQL] SQL 문자열 관련 함수 모음으로 MySQL, MariaDB 기준 입니다.

문자열함수 MySQL insert instr mid substring

MySQL 2025.05.04 86 회 읽음

MySQL 25.05.04 86

문자열의 자르거나 합치고 치환하는 많이 사용하는 함수를 정리하였습니다.

문자열을 이용하는 쿼리 사용이 필요한 경우 문자 자르기 치환 공백 제거 등 다양하게 활용할 수 있습니다. MySQL과 호환성이 좋은 MariaDB에서도 사용할 수 있습니다.

자주 사용하는 함수 이므로 습득 하거나 자주 활용하는 것을 추천 합니다.

MySQL 문자열 관련 함수 요약

ASCII(문자) : 문자의 아스키 코드 값 리턴 한다.

SELECT ASCII('문자');

CONCAT('문자열1','문자열2','문자열3'...) : 문자열들을 이어준다.

select concat('ASP,','PHP,','SQL',' WEB STUDY');

INSERT('문자열','시작 위치','길이','새로운 문자열') : 문자열의 시작 위치부터 길이만큼 새로운 문자열로 대치 해주며 '시작 위치' 와 '길이'는 문자열이 아니므로 작은 따옴표로 굳이 묶어주지 않아도 된다.

select insert('MySql web study','7','3','offline');
select insert('MySql web study',7,3,'offline');

REPLACE('문자열','기존 문자열','바뀔 문자열') : 문자열 중 기존 문자열을 바뀔 문자열로 바꾼다.

select replace('MySql web study','web','offline');

INSTR('문자열','찾는 문자열') : 문자열 중 찾는 문자열의 위치 값을 출력하며 값이 존재하지 않으면 0값 리턴 한다.

select instr('MySql web study','s');
select instr('MySql web study','S');

LEFT('문자열',개수) : 문자열 중 왼쪽에서 개수 만큼을 추출한다.

select left('MySql web study',5);
select left('MySql web study','5');

RIGHT('문자열',개수) : 문자열 중 오른쪽에서 개수 만큼을 추출.

select right('MySql web study',5);
select right('MySql web study','5');

MID('문자열',시작 위치,개수) : 문자열 중 시작 위치부터 개수만큼 출력해 준다.

select mid('MySql web study',7,3);
select mid('MySql web study','7','3');

SUBSTRING('문자열',시작 위치,개수) : 문자열 중 시작 위치부터 개수만큼 출력

select substring('Mysql web study',11,5);
select substring('Mysql web study','11','5');

LTRIM('문자열') : 문자열 중 왼쪽의 공백을 없앤다.

select ltrim(' web study');

RTRIM('문자열') : 문자열 중 오른쪽의 공백을 없앤다.

select rtrim('web study ');

TRIM('문자열') : 양쪽 모두의 공백을 없앤다.

select trim(' web study ');

LCASE('문자열') 또는 LOWER('문자열') : 소문자로 바꾼다.

select lcase('MYSQL');
select lower('MySQL');

UCASE('문자열') 또는 UPPER('문자열') : 대문자로 바꾼다.

select ucase('mySql'); 
select upper('mysql');

REVERSE('문자열') : 문자열을 반대로 나열한다. 예) REVERSE('abcde') ==> edcba

select reverse('lqSyM');

문자열 관련 함수 명령과 출력 결과 예제

문자열 함수에서 사용되는 문자열의 길이가 MySQL 서버 파라미터 중의 max_allowed_packet값보다 크면 함수의 결과로 NULL 이 리턴 된다. 그리고 모든 문자열 함수에서 문자열의 첫 번째 문자의 위치 값은 숫자로 1이다. 0이 아님에 유의하자.

또한 참고로 알아둘 것은 일반적으로 얘기할 때 '문자' 와 '문자열' 은 의미가 다르다. 여기서 말하는 '문자' 는 한 character 의 문자 즉 문자 하나를 의미하며 '문자열' 은 여러 개의 character로 이루어진 문자열은 의미한다.

ASCII(str)

문자열 str 의 가장 왼쪽에 있는 문자의 아스키 코드 값을 가져온다. str 에 문자 대신 숫자가 들어가면 숫자를 문자열 형태로 변환하여 가장 왼쪽 문자의 아스키 코드 값을 가져온다.

mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+
1 row in set (0.00 sec)
mysql>

ORD(str)

문자열 str 의 가장 왼쪽에 있는 문장의 아스키 코드 값을 가져온다. 특히 한글과 같이 2byte 이상으로 이루어진 문자에 대해서도 비트 연산을 통해 값을 가져올 수 있다. 따라서 한글과 같은 문자의 아스키 값을 가져올 때 사용할 수 있다.

mysql> select ord('가');
+-----------+
| ord('가') |
+-----------+
|       176 |
+-----------+
1 row in set (0.00 sec)
mysql>

CONV(N, from_base, to_base)

from_base 진수로 N 인 수를 to_base 진수로 변환한다. 예를 들어 CONV(5, 10, 2) 는 10 진수로 5 인수를 2 진수로 변환한 값 101 을 가져온다.

mysql> select conv(5,10,2);
+--------------+
| conv(5,10,2) |
+--------------+
| 101          |
+--------------+
1 row in set (0.44 sec)
mysql>

BIN(N)

N 을 2 진수로 표현한 문자열을 가져온다.

mysql> select bin(12);
+---------+
| bin(12) |
+---------+
| 1100    |
+---------+
1 row in set (0.00 sec)
mysql>

OCT(N)

N 을 8 진수로 표현한 문자열을 가져온다.

mysql> select oct(12);
+---------+
| oct(12) |
+---------+
| 14      |
+---------+
1 row in set (0.00 sec)
mysql>

HEX(N_or_S)

N_or_S 가 숫자이면 16 진수로 표현한 문자열을 가져오고, N_or_S 가 문자열이면 16 진수 숫자를 가져온다.

mysql> select hex(255), hex('abc');
+----------+------------+
| hex(255)  | hex('abc')|
+----------+------------+
| FF          | 616263  |
+----------+------------+
1 row in set (0.00 sec)
mysql>

CHAR(N, ...)

아스키 코드 값 N 을 문자로 변환한 뒤 합쳐서 하나의 문자열을 가져온다.

mysql> select char(77,121,83,81,76);
+-----------------------+
| char(77,121,83,81,76) |
+-----------------------+
| MySQL                 |
+-----------------------+
1 row in set (0.00 sec)
mysql>

CONCAT(str1, str2, ...)

문자열을 합친다. str 이 숫자형인 경우 문자형으로 변환한 뒤 합친다.

 mysql> select concat('My','S','QL');
+-----------------------+
| concat('My','S','QL') |
+-----------------------+
| MySQL                 |
+-----------------------+
1 row in set (0.00 sec)
mysql>

CONCAT_WS(separator, srt1, str2, ...)

문자열 사이에 구분자를 두어 합친다.

 mysql> select concat_ws('_','MySQL','Database');
+-----------------------------------+
| concat_ws('_','MySQL','Database') |
+-----------------------------------+
| MySQL_Database                    |
+-----------------------------------+
1 row in set (0.01 sec)
mysql>

LENGTH(str)혹은

OCTET_LENGTH(str) 혹은 CHAR_LENGTH(str) 혹은 CHARACTER_LENGTH(str) 문자열의 길이를 가져온다.

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

BIT_LENGTH(str)

문자열의 길이를 bit 값으로 가져온다.

mysql> select bit_length('text');
+--------------------+
| bit_length('text') |
+--------------------+
|                 32 |
+--------------------+
1 row in set (0.00 sec)
mysql>

LOCATE(substr,str) 혹은 POSITION(substr IN str)

str 에서 substr 이 처음 나타나는 지점의 위치를 가져온다.

mysql> select locate('bar','foobarbar');
+---------------------------+
| locate('bar','foobarbar') |
+---------------------------+
|                         4 |
+---------------------------+
1 row in set (0.11 sec)
mysql>

LOCATE(substr, str, pos)

str 에서 pos 위치부터 시작해서 substr 이 처음 나타나는 지점의 위치를 가져온다.

mysql> select locate('bar','foobarbar',5);
+-----------------------------+
| locate('bar','foobarbar',5) |
+-----------------------------+
|                           7 |
+-----------------------------+
1 row in set (0.00 sec)
mysql>

INSTR(str, substr)

str 에서 substr 이 처음 나타나는 지점의 위치를 가져온다. 인자 순서만 바뀌었을 뿐 LOCATE(substr,str) 와 기능은 같다.

mysql> select instr('foobarbar','bar');
+--------------------------+
| instr('foobarbar','bar') |
+--------------------------+
|                        4 |
+--------------------------+
1 row in set (0.00 sec)
mysql>

LPAD(str, len, padstr)

문자열이 len 만큼 길이가 될 때까지 str 의 왼쪽에 padstr 을 계속 붙인다.

mysql> select lpad('hi',4,'??');
+-------------------+
| lpad('hi',4,'??') |
+-------------------+
| ??hi              |
+-------------------+
1 row in set (0.00 sec)
mysql>

RPAD(str, len, padstr)

문자열이 len 만큼 길이가 될 때까지 str 의 오른쪽에 padstr 을 계속 붙인다.

mysql> select rpad('hi',5,'?');
+------------------+
| rpad('hi',5,'?') |
+------------------+
| hi???            |
+------------------+
1 row in set (0.00 sec)
mysql>

LEFT(str, len)

str 문자열에서 len 길이만큼 왼쪽부터 잘라서 가져온다.

mysql> select left('foobarbar',5);
+---------------------+
| left('foobarbar',5) |
+---------------------+
| fooba               |
+---------------------+
1 row in set (0.00 sec)
mysql>

RIGHT(str, len)

str 문자열에서 len 길이만큼 오른쪽부터 잘라서 가져온다.

mysql> select right('foodbarbar',4);
+-----------------------+
| right('foodbarbar',4) |
+-----------------------+
| rbar                  |
+-----------------------+
1 row in set (0.00 sec)
mysql>

SUBSTRING(str, pos, len) 혹은 SUBSTRING(str FROM pos FOR len) 혹은 MID(str, pos, len)

문자열 str 에서 pos 위치부터 len 길이만큼 잘라낸다.

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

SUBSTRING(str, pos) 혹은 SUBSTRING(str FROM pos)

문자열 str 에서 pos 위치부터 끝까지 문자를 잘라낸다.

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

SUBSTRING_INDEX(str, delim, count)

문자열 str 에서 구분자 delim 의 count 번째 위치만큼 잘라낸다. count 가 양수이면 문자열의 왼쪽에서 부터 delim 의 순서를 세고, count 가 음수이면 문자열의 오른쪽에서 부터 delim의 순서를 센다.

mysql> select substring_index('www.mycql.com','.',2);
+----------------------------------------+
| substring_index('www.mycql.com','.',2) |
+----------------------------------------+
| www.mycql                              |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>

LTRIM(str)

문자열 왼쪽의 모든 공백을 제거한다.

mysql> select ltrim('   barabr');
+--------------------+
| ltrim('   barabr')       |
+--------------------+
| barabr                    |
+--------------------+
1 row in set (0.00 sec)
mysql>

RTRIM(str)

문자열 오른쪽의 모든 공백을 제거한다.

mysql> select rtrim('barbar   ');
+--------------------+
| rtrim('barbar   ') |
+--------------------+
| barbar             |
+--------------------+
1 row in set (0.00 sec)
mysql>

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

문자열 str 로부터 양쪽으로 모든 remstr 문자열을 제거한다. remstr 이 지정되지 않으면 공백을 제거한다. [BOTH | LEADING | TRAILING] 은 옵션으로서 BOTH 는 문자열 양쪽, LEADING 은 문자열 왼쪽, TRAILING 은 문자열 오른쪽의 모든 remstr 문자열을 제거한다.

 mysql> select trim('  bar  '), trim('bar' from 'barfoobar');
+-----------------+------------------------------------+
| trim('  bar  ')       | trim('bar' from 'barfoobar') |
+-----------------+------------------------------------+
| bar                    | foo                         |
+-----------------+------------------------------------+
1 row in set (0.00 sec)
mysql>

SPACE(N)

N 길이만큼 공백을 가져온다.

mysql> select space(6);
+----------+
| space(6) |
+----------+
|          |
+----------+
1 row in set (0.00 sec)
mysql>

REPLACE(str, from_str, to_str)

문자열 str 에서 from_str 문자열을 to_str 문자열로 바꾼다. 한글과 같은 다중 바이트 문자에도 잘 적용된다.

mysql> select replace('www.mysql.com','w','Ww');
+------------------------------------+
| replace('www.mysql.com','w','Ww')  |
+------------------------------------+
| WwWwWw.mysql.com                   |
+------------------------------------+
1 row in set (0.00 sec)
mysql>

REPEAT(str, count)

문자열 str을 count 번 반복하여 가져온다.

mysql> select repeat('MySQL',3);
+-----------------------+
| repeat('MySQL',3)     |
+-----------------------+
| MySQLMySQLMySQL       |
+-----------------------+
1 row in set (0.00 sec)
mysql>

REVERSE(str)

문자열 str 을 거꾸로 읽어서 가져온다.

mysql> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |
+----------------+
1 row in set (0.00 sec)
mysql>

INSERT(str, pos, len, newstr)

문자열 str 을 pos 위치부터 len 길이만큼 잘라낸 후 그 자리를 newstr 로 대체한다.

mysql> select insert('Quadratic',3,4,'what');
+--------------------------------+
| insert('Quadratic',3,4,'what') |
+--------------------------------+
| Quwhattic                      |
+--------------------------------+
1 row in set (0.08 sec)
mysql>

ELT(N, str1, str2 ,str3, ...)

str1, str2, str3, ... 중 N 번째 문자열을 가져온다. ELT() 함수는 FIELD() 함수와 상호 보완된다.

mysql> select elt(4,'ej','heja','hej','foo');
+--------------------------------+
| elt(4,'ej','heja','hej','foo') |
+--------------------------------+
| foo                            |
+--------------------------------+
1 row in set (0.02 sec)
mysql>

FIELD(str, str1, str2, str3, ...)

str1, str2, str3, ... 중 str 이 몇 번째 문자열 인가를 가져온다.

mysql> select field('ej','Hej','ej','Heja','hej','foo');
+-------------------------------------------+
| field('ej','Hej','ej','Heja','hej','foo') |
+-------------------------------------------+
|                                         2 |
+-------------------------------------------+
1 row in set (0.08 sec)
mysql>

FIND_IN_SET(str, strlist)

콤마(,) 로 구분된 문자열 strlist 에서 str 이 몇 번째 문자열 인가를 가져온다. 문자열 str 에 콤마가 들어가 있으면 제대로 동작하지 않는다.

mysql> select find_in_set('foo','he,my,foo,je,ke');
+--------------------------------------+
| find_in_set('foo','he,my,foo,je,ke') |
+--------------------------------------+
|                                    3 |
+--------------------------------------+
1 row in set (0.02 sec)
mysql>

LCASE(str) 혹은 LOWER(str)

문자열 str 은 소문자로 변환한다.

mysql> select lcase('MYSQL');
+-----------------+
| lcase('MYSQL')  |
+-----------------+
| mysql           |
+-----------------+
1 row in set (0.02 sec)
mysql>

UCASE(str) 혹은 UPPER(str)

문자열 str 을 대문자로 변환한다.

mysql> select ucase('mysql');
+----------------+
| ucase('mysql') |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)
mysql>

LOAD_FILE(file_name)

시스템 파일로부터 값을 읽어 들인다. 이 때 파일은 시스템에 존재해야 하며, file_name은 절대 경로로 표시되어야 한다. 또한 file 은 시스템의 읽기 권한이 있어야 하며 MySQL 사용자는 user 테이블의 FILE 권한이 있어야 한다. 이 조건들을 만족하지 못할 경우 NULL 을 가져온다.

QUOTE(str)

문자열 str 에 작은 따옴표 (') 가 들어 있으면 SQL 문장이 문법적으로 잘못될 수 있다. 예를 들어 INSERT 문에서 저장할 문자열 데이터에는 따옴표 (') 를 붙이는데 데이터 자체에 따옴표가 들어있는 경우 따옴표가 중복되어 INSERT 문이 제대로 실행되지 못한다. 그러한 경우를 방지하기 위해 QUOTE() 를 사용한다. QUOTE() 는 문자열에 작은 따옴표가 있으면 앞에 역슬레쉬 ()를 붙여서 작은 따옴표 문자(') 로 변환 시켜주는 함수이다.

mysql> select quote("mysql'df");
+-------------------+
| quote("mysql'df") |
+-------------------+
| 'mysql'df'        |
+-------------------+
1 row in set (0.00 sec)
mysql>

MySQL or MariaDb에서 사용하는 문자열 함수에 대해서 알아 보았습니다.

문자를 찾는 것 이외에도 제거하거나 필요한 부분만 사용할 때 활용하며 좋습니다.