[SQL] 문자열 변환 및 추출(UPPER, LOWER, INCAP, SUBSTR, LENGTH, INSTR, REPLACE, LPAD, RPAD, TRIM, RTRIM, LTRIM)

- 이 글은 도서 '초보자를 위한 SQL 200제'라는 책을 바탕으로 작성되었습니다.
- 이전 과정의 연습문제를 확인하고 싶으면 다음을 참고하시기 바랍니다.

[SQL]초보자를 위한 SQL 200제 문제풀이 모음

 

---

 

016 대소문자 변환 함수 배우기 - UPPER, LOWER, INTCAP

SQL에서 함수는 다양한 데이터 검색을 위해 필요한 기능입니다. 예를 들어 월급 평균값, 가장 크거나 작은 값 등을 일려면 함수를 알아야 합니다. 이러한 함수에는 하나의 행을 입력받아 하나의 행을 반환하는 단일행 함수와 여러 개의 행을 입력받아 하나의 행을 반환하는 다중 행 함수가 있습니다. 단일행 함수에는 문자 함수, 숫자 함수, 날짜 함수 등이 있고, 다중 행 함수에는 그룹 함수가 있습니다. 이 중 오늘 배우는 함수들이 단일행 함수들이 문자 함수에 해당합니다. 이를 정리하면 다음과 같습니다.

함수의 종류		설명
단일행 함수	정의	하나의 행을 입력받아 하나의 행을 반환하는 함수
	종류	문자 함수, 숫자 함수, 날짜 함수, 변환 함수, 일반 함수
다중 행 함수	정의	여러 개의 행을 입력받아 하나의 행을 반환하는 함수
	종류	그룹함수

여기서 문자 함수는 대소문자를 구분하는지 확인하기 위해 이름이 'SCOTT'인 사원의 정보를 검색할 경우, 대문자로 검색했을 때와 소문자로 검색했을 때의 차이를 확인해보겠습니다.

사원의 이름이 'SCOTT'인 경우

사원의 이름이 'scott'인 경우

위 결과처럼 문자 데이터의 경우 대소문자를 구분하기 때문에 사용자는 이를 염두하고 검색을 실시해야 한다. 이와 관련해서 오늘 알아볼 함수는 문자열을 대문자 또는 소문자로 출력하거나 첫 번째 철자만 대문자로 출력하게 하는 함수에 대해 알아보겠습니다.

UPPER(문자) : 문자열을 대문자로 출력한다
LOWER(문자) : 문자열을 소문자로 출력한다
INTCAP(문자) : 문자열에서 첫 번째 철자만 대문자로 출력, 나머지는 소문자로 출력한다.

 

Q. 사원 테이블의 이름을 출력하라. 단 첫 번째 컬럼은 이름을 대문자로 출력하고, 두 번째 컬럼은 이름을 소문자로 출력하고, 세 번째 컬럼은 이름의 첫 번째 철자는 대문자로, 나머지는 소문자로 출력하라.
A.

-- 첫 번째: 대문자, 두 번째 : 소문자, 세 번째: 대문자+소문자 
SELECT UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) FROM EMP;

 

Q. 이름이 scott인 사원의 이름과 월급을 조회하라
A.

LOWER(ENAME)을 통해 대문자였던 사원 이름 모두 소문자로 변환하여 소문자 scott을 확실하게 검색할 수 있게 한다.

SELECT ENAME, SAL FROM EMP WHERE LOWER(ENAME) = 'scott';

만약 문자 데이터가 대문자로 혹은 소문자로 되어있는지 확인이 어렵다면 WHERE LOWER(ENAME) = 'scott' OR UPPER(ENAME) = 'SCOTT';이라 작성하여 데이터를 확실하게 반환할 수 있다.

 

017 문자에서 특정 철자 추출하기 - SUBSTR

SUBSTR 함수는 문자에서 특정 위치의 문자열을 추출합니다.

SUBSTR('txt', start, count ) -- start: 추출할 철자의 시작 위치 -- count: 시작 위치로부터 추출할 철자의 개수

SUBSTR 함수의 다양한 옵션

1) 시작할 위치와 추출할 개수가 정해진 경우

SELECT SUBSTR('SMITH', 2,2) 

'SMITH' 문자에서 두 번째 철자부터 두 개를 출력하므로 'MI'를 추출하여 반환합니다.

2) 시작할 위치가 음수인 경우

SELECT SUBSTR('SMITH', -2,2) 

시작할 위치가 음수인 경우, 문자의 뒤에서부터 앞으로 오는 순서로 -2 자리인 'T'부터 두 개의 철자를 추출하여 'TH'를 추출하여 반환합니다.

3) 개수가 정해지지 않은 경우

SELECT SUBSTR('SMITH', 2)

개 수가 정해지지 않았기 때문에 'SMITH' 문자의 두 번째 자리은 'M'부터 끝까지 추출하여 'MITH'를 반환합니다.

Q. 영어 단어 SMITH에서 SMI만 잘라서 출력하라.
A.

'SMI'는 앞에서 첫 번째 철자부터 3개를 추출합니다.

 

S	M	I	T	H
1	2	3	4	5

 

SELECT SUBSTR('SMITH',1,3) FROM DUAL;

 

018 문자열의 길이 출력하기 - LENGTH

LENGTH 함수는 문자열의 길이를 출력하는 함수입니다. 문자열의 길이는 영어, 한글 상관없이 출력됩니다.

SELECT LENGTH('ABCDEFG'), LENGTH('가나다라마바사') FROM DUAL;

 

Q. 사원들의 이름을 출력하고 그 옆에 이름의 철자 개수를 출력하라.
A.

SELECT ENAME, LENGTH(ENAME) FROM EMP;

LENGTHB는 바이트의 길이를 반환합니다. 한글의 경우 한 글자에 3바이트이므로 만약 아래와 같이 입력하면 영문은 7바이트, 한글은 21을 반환합니다.

SELECT LENGTHB('ABCDEFG'), LENGTHB('가나다라마바사') FROM DUAL;

 

019 문자에서 특정 철자의 위치 출력하기 - INSTR

INSTR 함수는 문자에서 특정 철자의 위치를 출력하는 함수입니다.
예를 들어 사원 이름 'SMITH'에서 알파벳 철자 M이 몇 번째 자리에 있는지 확인하고 싶다면 찾아낼 문장 'SMITH'를 입력하고, 찾을 특정 철자인 'M'을 입력해 다음과 같이 작성하면 됩니다.

-- INSTR('문자열', '찾아낼 특정 철자') 
SELECT INSTR('SMITH','M') FROM DUAL;

Q. 이메일 주소에서 도메인 주소만을 추출하라.

1. '@'의 위치를 INSTR로 추출한다

SELECT INSTR('abcdefg@naver.com', '@') FROM DUAL;

 

2. 도메인 주소만을 추출하려면 '@' 이후부터 추출하는 것이므로 SUBSTR을 이용해 'naver.com'을 반환한다.

SELECT SUBSTR('abcdefg@naver.com', INSTR('abcdefg@naver.com', '@')+1) AS DOMAIN FROM DUAL;

 

a	b	c	d	e	f	g	@	n	a	v	e	r	.	c	o	m
1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17

 

※ 'NAVER'만 반환하는 법

위 과정에서 '.'의 위치를 찾아 하나 이전의 위치까지 찾

020 특정 철자를 다른 철자로 변경하기 - REPLACE

REPLACE 함수는 특정 절차를 다른 철자로 변경하는 문자 함수이다.

REPLACE(바꿀 철자가 포함된 대상, 바꿀 철자, 바뀔 철자)

예를 들어 사원번호에 포함된 '0'을 '-'로 바꾼다고 하면 아래와 같이 나타낼 수 있다.

REPLACE(DEPTNO, 0, '-')

 

Q. 사원들의 이름과 월급을 출력하라. 단 월급을 출력할 때 숫자 0을 *(별표)로 출력하라.

월급(SAL)의 0을 *으로 표시할 것이기 때문에 REPLACE(SAL, 0, '*') 순으로 입력한다.

SELECT ENAME, REPLACE(SAL,0,'*') FROM EMP;

REGEXP_REPLACE 함수는 정규식(Regular Expression) 함수로, 일반 함수보다 더 복잡한 형태의 검색 패턴으로 데이터를 조회할 수 있게 해주는 함수이다. 정규식에 경우 다양한 방법으로 표현될 수 있다.

REPLACE(바꿀 철자가 포함된 대상, 바꿀 정규식, 바뀔 철자)

 

예 1) 사원들의 이름과 월급을 출력하라, 단 월급의 숫자 0~3까지를 *로 출력하라

0부터 3까지의 숫자를 나타낼 때 '[0-3]'과 같이 정규식을 표현하여 REPLACE 함수와 같이 사용하면 된다.

SELECT ENAME, REGEXP_REPLACE(SAL, '[0-3]', '*') AS SALARY FROM EMP;

 

예 2) 이름의 두 번째 자리의 한글을 *로 출력하라.

다음 예제를 위해 아래와 같이 테이블 생성 스크립트를 실행한다.

CREATE TABLE TEST_ENAME (ENAME VARCHAR2(10)); 
INSERT INTO TEST_ENAME VALUES('김인호'); 
INSERT INTO TEST_ENAME VALUES('안상수'); 
INSERT INTO TEST_ENAME VALUES('최영희');

위의 테이블에서 ENAME 컬럼에서 이름의 두 번째 철자 반환하고, 반환된 두 번째 철자를 '*'로 바꾸는 것이 최종 목표이다. 출력 과정을 생각하며 SQL을 작성하면 다음과 같다.

SELECT REPLACE(ENAME, SUBSTR(ENAME,2,1), '*') FROM TEST_ENAME;

 

021 특정 철자를 N개만큼 채우기 - LPAD, RPAD

LPAD와 RPAD는 문자 출력 시 특정 철자를 N개만큼 채워서 출력한다. 그중에서 LPAD는 왼쪽(Left)에, RPAD는 오른쪽(Right)에 채워서(PAD) 출력한다. 예를 들어 1000이라는 숫자를 5개만큼 채워서 출력하고 나머지 자리에 '-'로 채워 출력할 경우 LPAD는 '-1000', RPAD는 '1000-'과 같은 방식으로 결과를 출력한다.

Q. 사원들의 이름과 월급을 출력하라. 단 월급 컬럼의 자릿수를 10자리로 하고, 월급을 출력하고 남은 나머지 자리에 *(별표)를 채워서 출력하라.

SELECT ENAME, LPAD(SAL, 10, '*') AS SAL1 , RPAD(SAL, 10, '*') AS SAL2 FROM EMP;

LPAD와 RPAD를 이용하면 SQL을 이용하여 데이터를 시각화하기 유용합니다.

Q. 사원들의 이름과 월급을 출력하라. 단 월급 100을 네모(■) 하나로 출력하라.

월급 100당 네모(■) 하나이므로 SAL을 100으로 나눈 몫이 네모의 개수가 됨을 이용합니다.

만약 ROUND(SAL/100)가 16이라면 전체 16자리를 확보합니다. 16 자리 중 ■ 하나 출력하고 나머지 15자리에 ■를 채워 출력하여 ■가 총 16개 출력합니다.

SELECT ENAME, LPAD('■',ROUND(SAL/100),'■') AS CHART FROM EMP

 

022 특정 철자 잘라내기 - TRIM, RTRIM, LTRIM

LTRIM은 문자 출력 시 왼쪽 철자를 입력받아 입력받은 철자를 잘라 출력하고, RTRIM은 오른쪽 철자를 잘라 출력합니다. 또한 TRIM은 양쪽의 철자를 잘라 출력합니다.
예)

SELECT LTRIM('다시 합창합니다','다'), RTRIM('다시 합창합니다','다'), TRIM('다' FROM'다시 합창합니다') FROM DUAL;

LTRIM의 경우 가장 왼쪽에 있는 '다'를 잘라서 출력했으며, RTRIM은 가장 오른쪽에 있는 '다'를 TRIM은 양쪽에 있는 '다'를 잘라냈음을 확인할 수 있다.

Q. 영어 단어 'SMITH'에 대해 첫 번째 컬럼은 철자를 출력하고, 두 번째 컬럼은 'S'를 잘라 출력하고, 세 번째 컬럼은 'H'를 잘라 출력하고, 네 번째 컬럼은 영어 단어 'SMITHS'에 대해 양쪽 'S'를 잘라 출력하라.

-- 1: 'SMITH', 2 : 'S'자름, 3 : 'H'자름, 4 : 'SMITHS'에서 양쪽 'S' 자름 
SELECT 'SMITH', LTRIM('SMITH','S'), 
	RTRIM('SMITH', 'H'), TRIM('S' FROM 'SMITHS')
FROM DUAL;

특정 철자를 잘라내는 LTRIM, RTRIM, TRIM은 주로 데이터의 공백을 없앨 때 이용합니다. 예시를 위해 아래의 SQL을 작성하여 실행합니다. 주의할 점은 이름에 해당하는 VALUE ('JACK ')에 공백을 포함하여 작성합니다.

INSERT INTO EMP(EMPNO, ENMAE, SAL, JOB, DEPTNO) VALUES(8291, 'JACK ', 3000, 'SALESMAN', 30); 

여기서 이름이 'JACK'인 사원의 정보를 조회하면 선택된 레코드가 없음을 확인할 수 있습니다.

SELECT * FROM EMP WHERE ENAME = 'JACK';

그 이유는 처음 JACK의 데이터를 입력할 때 오른쪽에 공백을 추가해서 입력했는데 WHERE절의 'ENAME = 'JACK''으로 비교할 때 공백 없이 비교했기 때문입니다. 우리가 데이터를 입력할 때 공백을 하나만 넣었다는 것을 알지만 만약 공백을 하나만 입력했는지, 여러 개를 입력했는지 모른다 할 때 JACK의 데이터를 검색하기 어렵습니다. 이럴 때 LTRIM, RTRIM, TRIM 함수를 이용하면 공백을 제거한 후 검색하기 때문에 JACK에 대한 데이터를 검색할 수 있습니다.

 SELECT * FROM EMP WHERE RTRIM(ENAME) = 'JACK';