[Oracle SQL] SQL 함수 - 단일행 함수 (1) 문자처리 함수

함수는 데이터를 조작하는 데 사용되며 기본적인 SQL문을 더욱 강력하게 해 줍니다. 함수는 입력(input)과 출력(output)으로 구성되며, 입력으로 설정된 데이터를 함수가 내부적으로 가공하여 출력을 통해 반환합니다. 

 

함수를 크게 두 가지로 나눌 수 있습니다. 단일행 함수는 모든 행에 대하여 각각 적용되어 행의 개수와 동일한 개수를 반환합니다. 다중행 함수(그룹 함수)는 검색되는 모든 행에 대하여 한 번만 적용되고 한 개의 결과만을 반환합니다. 아래 그림으로 확인할 수 있습니다.

 

 

단일행 함수는 SELECT, WHERE, ORDER BY절에 사용 가능하며, 처리하는 데이터 종류에 따라 문자처리 함수, 숫자처리 함수, 날짜처리 함수, 변환 함수, 일반 함수로 구분됩니다. 

 

이번 글에서는 문자처리 함수를 정리해 보겠습니다. 다음은 표로 간략히 정리한 문자처리 함수입니다.

 

함수	설명
INITCAP	첫 글자만 대문자로 변환하여 반환
UPPER	모든 글자를 대문자로 변환하여 반환
LOWER	모든 글자를 소문자로 변환하여 반환
CONCAT	두 개의 문자열을 연결하여 반환
LENGTH	문자열의 길이를 반환
INSTR	특정문자의 위치를 반환
SUBSTR	문자의 일부분을 반환
REPLACE	특정 문자열을 치환하여 반환
LPAD	오른쪽 정렬 후 왼쪽에 생긴 공백에 특정 문자를 채움
RPAD	왼쪽 정렬 후 오른쪽에 생긴 공백에 특정 문자를 채움
LTRIM	왼쪽에서 특정 문자를 삭제
RTRIM	오른쪽에서 특정 문자를 삭제
TRIM	양쪽에 있는 특정 문자를 삭제

 

 

 

1. INITCAP 함수

 

각 문자열의 첫 글자를 대문자로 변경하고 나머지는 소문자로 변경하여 반환합니다. 'Oracle Sql'이 반환됩니다.

SELECT INITCAP('ORACLE SQL') FROM dual;  --> Oracle Sql

 

 

2. UPPER 함수

 

모든 글자를 대문자로 변경하여 반환합니다. 'ORACLE SQL'이 반환됩니다.

SELECT UPPER('Oracle Sql') FROM dual;  --> ORACLE SQL

 

이름이 KING인 사람을 검색하고 싶을 때 WHERE절에서 대소문자를 정확히 입력해야 하는 번거로움이 있었습니다. 이런 경우에 UPPER 함수를 이용하면 대문자만 사용하면 되므로 편하게 검색할 수 있게 됩니다.

SELECT ename, sal FROM emp WHERE UPPER(ename)='KING';

 

 

3. LOWER 함수

 

모든 글자를 소문자로 변경하여 반환합니다. 'oracle sql'이 반환됩니다.

SELECT LOWER('Oracle Sql') FROM dual;  --> oracle sql

 

LOWER 함수도 마찬가지로 대소문자를 정확히 알 수 없을 때 사용할 수 있습니다. 

SELECT ename, sal FROM emp WHERE LOWER(ename)='king';

 

 

4. CONCAT 함수

 

두 개의 문자열을 연결하여 하나의 문자열로 반환합니다. Concatenation의 약자이며, 연결 연산자(||)와 동일한 기능을 수행합니다. 'OracleSql'이 반환됩니다. 

SELECT CONCAT('Oracle','Sql') FROM dual;  --> OracleSql

 

 

5. LENGTH 함수 ★★★

 

문자열의 길이를 숫자 값으로 반환합니다. 총 여섯 자리이므로 6이 반환됩니다.

SELECT LENGTH('Oracle') FROM dual;  --> 6

 

공백을 포함하여 총 일곱 자리이므로 7이 반환됩니다.

SELECT LENGTH('Ora cle') FROM dual;  --> 7

 

다음과 같이 이름의 길이를 출력할 수 있습니다.

SELECT ename, LENGTH(ename) FROM emp;

 

 

6. INSTR 함수

 

문자열에서 특정 문자가 나타나는 위치를 반환합니다. 

INSTR(컬럼명|표현식, 검색값, [m, n])

m은 문자를 검색하기 위한 시작 위치값을 의미하고, n은 몇 번째로 나오는 문자를 검색할지를 의미합니다. m과 n값은 생략할 수 있으며, 생략하면 첫 번째 문자부터 시작해서 첫 번째로 검색되는 문자의 위치값을 반환합니다. 

 

첫 번째 문자부터 시작해서 두 번째로 검색되는 L을 찾는 함수이므로, 두 번째 L의 위치값인 4가 반환됩니다. 

SELECT INSTR('MILLER','L',1,2) FROM dual;  --> 4

 

m, n값을 생략하였으므로 첫 번째 문자부터 시작해서 첫 번째로 검색되는 L을 찾습니다. 첫 번째 L의 위치값인 3이 반환됩니다.

SELECT INSTR('MILLER','L') FROM dual;  --> 3

 

첫 번째 문자부터 시작해서 두 번째로 검색되는 X를 찾는데, 해당 문자열에 X가 없으므로 0이 반환됩니다.

SELECT INSTR('MILLER','X',1,2) FROM dual;  --> 0

 

 

7. SUBSTR 함수 ★★★

 

문자열에서 일부분의 문자열을 추출할 때 사용합니다. 

SUBSTR(컬럼명|표현식, m [,n])

m은 문자열을 검색하기 위한 시작 위치값을 의미하고, n은 반환받을 문자열의 개수를 의미합니다. n값은 생략할 수 있으며, 생략하면 시작 위치값 이후의 모든 문자열이 반환됩니다. m이 음수일 경우에는 뒤에서 m번째 문자부터 반대 방향(정방향)으로 n개의 문자를 반환합니다. 

 

주민번호에서 성별을 나타내는 숫자를 추출할 때 사용할 수 있습니다. 여덟 번째 자리인 1이 반환됩니다. 

SELECT SUBSTR('900303-1234567',8,1) FROM dual;  --> 1

 

n을 생략하여 여덟 번째 자리부터 모두 반환되기 때문에, 주민번호 뒷자리인 1234567을 모두 반환하게 됩니다.

SELECT SUBSTR('900303-1234567',8) FROM dual;  --> 1234567

 

뒤에서 일곱 번째 자리부터 모든 문자열을 반환합니다. 위의 결과값과 마찬가지로 1234567을 반환하게 됩니다.

SELECT SUBSTR('900303-1234567',-7) FROM dual;  --> 1234567

 

뒤에서 두 번째 자리부터 한 개의 문자를 반환합니다. 뒤에서 두 번째 자리인 6이 반환됩니다. 

SELECT SUBSTR('900303-1234567',-2,1) FROM dual;  --> 6

 

입사일에서 입사연도만을 추출할 수 있습니다.

SELECT ename, SUBSTR(hiredate,1,2) "입사연도" FROM emp;

 

이름의 앞 세 자리만 추출해서 회사 계정을 만들어볼 수도 있습니다.

SELECT SUBSTR(ename,1,3)||empno||'@oracle.com' "회사 계정" FROM emp;

 

 

8. REPLACE 함수

 

특정 문자열을 치환할 때 사용합니다. 

REPLACE(컬럼명|표현식, 's1', 's2')

 

'JACK and JUE' 문자열에서 J 문자열을 찾아내 J를 BL 문자열로 치환합니다. 'BLACK and BLUE'가 반환됩니다. 

SELECT REPLACE('JACK and JUE','J','BL') FROM dual;  --> BLACK and BLUE

 

 

9. LPAD 함수

 

문자열을 오른쪽 정렬하고 남은 공백에 특정 문자를 왼쪽부터 채워 넣습니다.

LPAD(컬럼명|표현식, n, 'str')

n은 반환되는 문자열의 전체 자릿수를 의미하고, str은 삽입할 문자를 의미합니다. 삽입할 문자를 지정하지 않으면 공백으로 채워지며, 문자열의 길이가 n보다 클 경우 해당 문자열을 n개 문자열만큼 반환합니다. 

 

다음의 함수를 해석하면 총 열 자리를 마련해 놓고, 'MILLER'를 오른쪽 정렬해서 채운 후, 나머지 왼쪽의 네 자리를 '*'로 채우는 것입니다. '****MILLER'가 반환됩니다.

SELECT LPAD('MILLER',10,'*') FROM dual;  --> ****MILLER

 

특정 문자를 남는 공간만큼 채우는 것이므로 다음의 문자열에서 5는 출력되지 않습니다.

SELECT LPAD('MILLER',10,12345) FROM dual;  --> 1234MILLER

 

 

10. RPAD 함수

 

LPAD와 반대로 문자열을 왼쪽 정렬한 후 특정 문자를 남은 오른쪽 공백에 채워 넣습니다.

SELECT RPAD('MILLER',10,'*') FROM dual; --> MILLER****

SELECT RPAD('MILLER',10,12345) FROM dual;  --> MILLER1234

 

이름의 앞 세 자리만 표시하고 나머지는 숨김처리하기 위해 다음과 같은 SQL문을 사용할 수 있습니다.

SELECT RPAD(SUBSTR(ename,1,3),10,'*') "이름" FROM emp;

 

다음은 주민번호 '900303-1234567'을 '900303-1******'로 출력하는 여러 방법들입니다. 

SUBSTR 함수로 첫 번째 문자부터 여덟 번째 문자까지 추출한 후 연결 연산자를 사용해 * 표시를 붙이는 방법입니다.

SELECT SUBSTR('900303-1234567',1,8)||'******' 
	"주민번호" FROM dual;  --> 900303-1******

 

이번엔 연결 연산자 대신 CONCAT 함수를 사용해 연결시킨 SQL문입니다.

SELECT CONCAT(SUBSTR('900303-1234567',1,8),'******') 
	"주민번호" FROM dual;  --> 900303-1******

 

SUBSTR 함수로 첫 번째 문자부터 여덟 번째 문자까지 추출한 후, RPAD 함수로 열네 자리를 만들어서, 추출한 문자열은 왼쪽 정렬을 하고 남은 오른쪽 공백을 * 표시로 채운 SQL문입니다.

SELECT RPAD(SUBSTR('900303-1234567',1,8),14,'*') 
	"주민번호" FROM dual;  --> 900303-1******

 

SUBSTR 함수로 아홉 번째 문자부터 끝까지 추출한 것(234567)을 REPLACE 함수를 사용하여 * 표시로 치환하는 SQL문입니다.

SELECT REPLACE('900303-1234567',SUBSTR('900303-1234567',9),'******') 
	"주민번호" FROM dual;  --> 900303-1******

 

 

11. TRIM 함수

 

양쪽으로 특정 문자열을 삭제할 때 사용합니다.

SELECT TRIM('0' from '0001234567000') FROM dual;  --> 1234567

 

삭제할 문자를 지정하지 않으면 기본적으로 양쪽 공백이 제거됩니다. 

SELECT TRIM('  hello  ') FROM dual;  --> hello

 

LENGTH 함수로 양쪽 공백이 제거되었음을 확인할 수 있습니다.

SELECT LENGTH('  hello  '), LENGTH(TRIM('  hello  ')) FROM dual;

 

 

단일행 함수 중 문자처리 함수에 대해 알아보았습니다. 다음 글에서는 숫자처리 함수를 정리해 보겠습니다.