테크정보
[SQL] SQL 문자열 관련 함수 모음으로 MySQL, MariaDB 기준 입니다.
문자열의 자르거나 합치고 치환하는 많이 사용하는 함수를 정리하였습니다.
문자열을 이용하는 쿼리 사용이 필요한 경우 문자 자르기 치환 공백 제거 등 다양하게 활용할 수 있습니다. 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>
