[SQL] 문자 함수, 숫자 함수

안녕하세요. 이번 글은 문자 및 숫자 함수에 대한 설명입니다.

- 문자 함수

• 문자 함수 : 문자 데이터를 가공 및 응용을 통해 문자 데이터로부터 특정 결과를 얻기 위해 사용하는 함수

1) 영문자의 대소문자를 다뤄주는 함수

• UPPER(문자열) : 문자 데이터 전체를 대문자로 전환 후 출력하는 함수
• LOWER(문자열) : 문자 데이터 전체를 소문자로 전환 후 출력하는 함수
• INITCAP(문자열) : 문자 데이터의 첫 글자만 대문자로 나머지는 소문자로 변환 후 출력하는 함수

select UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) from EMP;

다음 코드는 EMP 테이블에서 ENAME의 영문자를 각각 대문자, 소문자 그리고 첫 글자만 대문자로 나머지는 소문자로 변환해서 반환하라는 뜻이다. 실행 결과로 각각의 함수에 맞게 정상적으로 변환된 후 조회되는 점을 확인할 수 있다.

2) 문자열의 길이를 구해주는 함수

• LENGTH(문자열) : 문자열의 길이를 반환하는 함수
• LENGTHB(문자열) : 문자열의 Byte수를 반환하는 함수

select ENAME, LENGTH(ename), LENGTHB(ename), DEPTNO from EMP;

다음 코드는 EMP 테이블에서 ENAME과, ENAME의 문자열 길이, 바이트 수 그리고 DEPTNO를 출력하는 뜻을 가지고 있다. 실행 결과를 보면 각각의 문자열에 맞게 문자열의 길이와 바이트 수가 정상적으로 출력되었음을 확인할 수 있다.

이때 맨 마지막에 한글이름의 문자열 바이트수가 9Byte인 것을 볼 수 있는데 한글은 한 글자 당 3Byte로 산정된다.

3) 문자열에서 문자의 위치를 찾아주는 함수 

• INSTR(문자열, 찾으려는 문자, 찾기 시작점) : 문자열에서 찾으려는 문자를 시작점에서부터 찾아서 위치값을 반환하는 함수

select ENAME, INSTR(ENAME, 'I', 2) from EMP;

해당 코드는 EMP 테이블에서 ENAME과 ENAME에서 2번째 위치부터 I가 있는 위치값을 출력하라는 뜻을 가지고 있다. 해당 코드를 실행해보면 기존 ENAME과 INSTR() 함수를 활용한 결과를 확인할 수 있다. 이때 찾고자 하는 문자가 행에 존재하지 않는 경우도 있는데 이럴 때는 위치값이 존재하지 않는다는 뜻으로 0을 반환해준다.

select ENAME, INSTR(ENAME, 'A') from EMP;

이때 찾기 시작점은 선택 사항인데 이를 생략할 경우 처음 위치부터 찾는다는 것을 확인할 수 있다. 그리고 만약 찾으려는 문자가 문자열에 존재하지 않으면 위치 값이 존재하지 않는다는 뜻으로서 0을 반환한다.

4) 문자열 일부만 구해주는 함수

• SUTSTR(문자열, 시작 위치, 추출 길이) : 문자열에서 시작 위치부터 추출 길이만큼 자른 결과를 반환하는 함수

select ENAME, SUBSTR(ENAME, 3, 2) from EMP;

다음 코드는 EMP 테이블에서 ENAME과 ENAME에서 3번째 자리를 시작으로 두 개의 문자를 자른 결과를 변환해서 출력하라는 뜻을 가지고 있다. 해당 코드를 실행해보면 ENAME과 SUBSTR 함수를 통해 시작하는 자리부터 2개까지 자른 결과를 정상적으로 출력되는 점을 확인할 수 있다. 이때 특정 위치부터 시작하고 2개와 같이 문자의 갯수를 지정했음에도 이후로 그만큼의 길이가 없으면 딱 출력한 부분까지가 문자열의 끝임을 의미한다.

select ENAME, SUBSTR(ENAME, 3) from EMP;

이때 추출 길이는 선택사항으로 생략이 가능하다. 생략할 경우 문자열의 끝까지 반환하게 된다.

select ENAME, SUBSTR(ENAME, -2) from EMP;

또한 시작할 위치를 음수를 입력함으로서 맨 뒤에서부터 좌측 방향으로 위치를 지정할 수 있다. 소스코드에서 시작 위치를 -2로 지정했는데 -2는 맨 뒤에서부터 2번째 위치부터 시작함을 의미한다.

5) 특정 문자 -> 다른 문자로 바꿔주는 함수

• REPLACE(문자열, 바꿀 대상, 대체 문자) : 문자열에서 찾는 문자를 바꿀 대상을 대체 문자로 바꿔서 출력하는 함수

select ENAME, REPLACE(ENAME, 'T', '_') from EMP;

다음 코드는 EMP 테이블에서 ENAME과 ENAME에서 T라는 문자를 찾아서 이를 _로 변환한 결과를 조회하는 뜻을 가지고 있다. 해당 코드를 실행해보면 기존 ENAME과 REPLACE 함수를 사용한 결과를 정상적으로 조회할 수 있음을 확인할 수 있다. 이때 홍길동과 FORD처럼 찾는 문자가 존재하지 않는 데이터의 경우에는 아무 변화없이 그대로 출력되는 점도 확인할 수 있다.

6) LPAD, RPAD

• LPAD(문자열, 자릿수, 공백에 들어갈 문자) : 자릿수 내에 데이터가 있으면서 빈 공간을 문자로 채워서 반환하는 함수
• RPAD(문자열, 자릿수, 공백에 들어갈 문자) : 자릿수 내에 데이터가 있으면서 빈 공간을 문자로 채워서 반환하는 함수

select LPAD('Tistory', 15, '*') as "EXAMPLE_1", RPAD('Tistory', 15, '*') as "EXAMPLE_2" from DUAL;

다음 코드는 좌우 15개의 공간에 Tistory라는 문자열을 넣고 남은 공간 *으로 채운 결과를 출력하라는 뜻을 가지고 있다. 해당 코드를 실행해서 결과를 확인해보면 문자열과 채워넣을 값의 수를 세서 15개라는 점을 확인할 수 있다.

select LPAD('Tistory', 15) as "EXAMPLE_1", RPAD('Tistory', 15) as "EXAMPLE_2" from DUAL;

이때 공백에 들어갈 문자는 선택 사항인데 이를 생략할 경우 생략한 그대로 공백으로 채워지는 것을 확인할 수 있다.

7) 문자열 + 문자열 = ?

• CONCAT(문자열, 붙일 문자열) : 기존 데이터에 새로운 문자열을 합친 결과를 반환해서 출력하는 함수

select ENAME, CONCAT(ENAME, '_NEW') from EMP;

해당 코드는 EMP 테이블에서 ENAME과 ENAME에 _NEW를 합친 결과를 반환해서 출력하라는 뜻을 가지고 있다.

실행 결과를 보면 기존 ENAME과 ENAME + _NEW를 붙인 결과가 정상적으로 출력되는 점을 확인할 수 있다.

select ENAME, ENAME || '_NEW' from EMP;

같은 역할의 연산자로 '||'가 있는데 해당 연산자 또한 CONCAT() 함수와 같은 역할을 한다는 것을 알 수 있다.

8) 특정 글자만 누끼하다?

• TRIM(삭제옵션 삭제할 문자 FROM 문자열) : 문자열에서 삭제할 문자를 옵션을 기반으로 삭제 후 반환하는 함수
• LTRIM(문자열, 삭제할 문자) : 문자열에서 삭제할 문자를 찾아서 해당 부분만 삭제 후 반환해서 출력하는 함수
• RTRIM(문자열, 삭제할 문자) : 문자열에서 삭제할 문자를 찾아서 해당 부분만 삭제 후 반환해서 출력하는 함수

TRIM를 설명하기 앞서 LTRIM과 RTIRM 함수에 대한 설명부터 하겠다.

select LTRIM('____Oracle______', '_') as "Left", RTRIM('____Oracle______', '_') as "Right" from DUAL;

해당 코드는 각 문자열에서 _이 있으면 왼쪽과 오른쪽 각각의 방향에서 제거한 결과를 출력하라는 뜻을 가지고 있다. 이를 실행해서 결과를 확인해보면 왼쪽과 오른쪽 양 방향으로 각각 _이라는 문자가 정상적으로 제거됨을 확인할 수 있다.

select LTRIM('    Oracle    ') as "Left", RTRIM('     Oracle     ') as "Right" from DUAL;

이때 삭제할 문자는 선택사항으로서 생략할 경우 원본 문자열 데이터에서 공백을 찾아서 빈 공간만 삭제한다는 점을 확인할 수 있다. 

select TRIM(LEADING FROM '     Oracle   ') as "Trim_1", 
TRIM(TRAILING FROM '     Oracle   ') as "Trim_2", 
TRIM(BOTH FROM '     Oracle   ') as "Trim_3" from DUAL;

다음 코드는 문자열에서 각각 왼쪽, 오른쪽 그리고 양방향에 있는 특정 문자를 지운 결과를 출력하라는 뜻을 가지고 있다. 여기서 FROM 앞에 제거할 특정 문자를 입력하는데 이를 생략하면 공백을 지우라는 뜻으로 간주된다. 이를 실행해서 결과를 확인해보면 각 좌측과 우측 그리고 양방향으로 공백이 제거된 결과가 출력되는 점을 확인할 수 있다.

select TRIM(LEADING '_' FROM '_____Oracle___') as "Trim_1", 
TRIM(TRAILING '_' FROM '_____Oracle___') as "Trim_2", 
TRIM(BOTH '_' FROM '_____Oracle___') as "Trim_3" from DUAL;

다음과 같이 FROM 앞에 삭제할 문자를 지정해서 대상 문자열에서 삭제할 문자가 있을 때 제거될 수 있다는 점을 확인할 수 있다. 만약 삭제할 지정 문자가 대상 문자열에 존재하지 않으면 처음 모습 그대로 출력된다는 점도 기억하자.

 

+ TRIM에서 사용할 수 있는 삭제 옵션

• LEADING : 리드라는 뜻으로서 왼쪽에 있는 글자를 제거하는 옵션
• TRAILING : 꼬리라는 뜻으로서 오른쪽에 있는 글자를 제거하는 옵션
• BOTH : 양쪽에 있는 글자를 제거하는 옵션

- 숫자 함수

1) 반올림과 버림 숫자 함수

• ROUND(숫자, 반올림할 위치[선택]) : 숫자를 위치에서 반올림하는 함수이며 위치를 생략하면 소수점 첫째 자리에서 반올림을 진행한다.
• TRUNC(숫자, 버릴 위치[선택]) : 숫자를 위치에서 버림 처리하는 함수이며 위치를 생략하면 소수점 첫째 자리에서 버림을 진행한다.

select ROUND(30.51548), ROUND(31.954, 1), ROUND(31.954, -1) from DUAL;

해당 코드를 실행함으로써 ROUND 함수를 사용해서 반올림 위치에 따라 반올림한 결과를 확인할 수 있다.

select TRUNC(30.51548), TRUNC(31.954, 1), TRUNC(31.954, -1) from DUAL;

해당 코드를 실행함으로써 TRUNC 함수를 사용해서 버릴 위치에 따라 버림 처리의 결과를 확인할 수 있다.

여기서 반올림 및 버림 함수를 사용할 때 소수점을 기준으로 위치를 지정하는데 정수와 소수를 구분하는데 . 을 기준점으로 정수가 있는 왼쪽 방향으로는 -1부터 시작되며 소수가 있는 오른쪽 방향으로는 0부터 1씩 증가하는 방식으로 시작된다.

예를 들어 상단 이미지에 있는 실수를 기준으로 2번째 자리에서 반올림한다면 5이상은 올림 처리하기 때문에 316.21이 나온다는 것을 알 수 있다. (개인적으로 해당 이미지를 참고하면서 위치값을 파악하는 것도 좋다고 생각한다.)

2) 올림과 내림 숫자 함수

• CEIL(숫자) : 숫자와 가까운 큰 정수를 반환하는 함수 (음수이면 0에 가까운 값 반환)
• FLOOR(숫자) : 숫자와 가까운 작은 정수를 반환하는 함수 (음수이면 0에서 먼 값 반환)

select CEIL(50.9), CEIL(-56.6), FLOOR(42.9), FLOOR(-30.2) from DUAL;

다음과 같이 숫자에 대한 올림과 내림에 대한 결과를 확인할 수 있다. 이때 대표적으로 -30.2의 내림은 -31이라고 나오는데 이는 -30.2보다 큰 정수 중 가장 작은 정수를 반환한다고 해석할 수 있다.

3) 숫자와 숫자를 나눠서 남는 나머지 값을 구하는 함수

• MOD(숫자1, 숫자2) : 숫자1에 숫자2를 나눠서 나오는 나머지 값을 반환하는 함수

select MOD(10, 2), MOD(10, 3) from DUAL;

다음과 같이 숫자에 숫자를 나눠서 나오는 나머지 값을 확인할 수 있다. 그 외에도 ABS 같이 다양한 숫자 함수가 존재한다.

 

다음 글은 날짜 함수에 대한 설명입니다.