문자 함수

#핵심 요약

함수는 더 정밀한 데이터 검색을 위해 씀

대소문자: UPPER, LOWER, INITCAP
자르기와 연결: SUBSTR, CONCAT, ||
길이와 위치: LENGTH, INSTR
바꾸기: REPLACE
채우기: LPAD, RPAD
잘라내기: TRIM, LTRIM, RTRIM

DUAL은 결과값만 보기 위한 가상의 테이블임

LTRIM, RTRIM의 두 번째 인수는 잘라낼 "문자열"이 아니라 "문자 집합"임
RTRIM(email, '.com')은 끝에서 ., c, o, m 중 어느 글자든 계속 지움

#개념과 동작 원리

SUBSTR: 시작 위치가 0이면 1로 봄
음수면 끝에서부터 셈
개수를 생략하면 끝까지, 1보다 작으면 NULL을 돌려줌
길이는 입력 문자 집합의 "문자" 단위로 셈(바이트 단위는 SUBSTRB)

RTRIM(char, set): char의 오른쪽 끝에서 set에 들어 있는 모든 문자를 지움
set을 생략하면 공백 하나임
LTRIM도 같은 방식임
공식 예: RTRIM('<=====>BROWNING<=====>', '<>=') → <=====>BROWNING

#필수 명령어 또는 SQL

#대소문자와 자르기

SELECT UPPER(ename), LOWER(ename), INITCAP(ename)
  FROM emp;

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

SELECT UPPER(SUBSTR(ename, 1, 1)) || LOWER(SUBSTR(ename, 2)) AS 이름
  FROM emp;

결과 해석: 두 번째 문장은 이름을 소문자로 입력해도 대문자로 저장된 이름을 찾음
세 번째 문장은 INITCAP을 쓰지 않고 같은 결과를 만드는 실습 과제의 답임

#연결, 길이, 위치

SELECT CONCAT(ename, sal), ename || '  ' || sal
  FROM emp;

SELECT ename, LENGTH(ename)
  FROM emp;

SELECT email, INSTR(email, '@')
  FROM emp19;

결과 해석:

설명: CONCAT은 두 값만 연결할 수 있어 Oracle에서는 ||를 주로 씀
PostgreSQL에서는 ||에 NULL이 섞이면 결과가 모두 NULL이 되어 CONCAT을 많이 씀

실습에서는 @가 없는 이메일을 찾는 두 방법(INSTR(email, '@') = 0, email NOT LIKE '%@%')의 성능을 실행 계획의 버퍼 수로 비교했음
그 준비로 SYS에서 ALTER SYSTEM SET statistics_level = all을 실행했음
이 설정은 인스턴스 전체에 영향을 주는 변경임

SELECT ename, email
  FROM emp19
 WHERE INSTR(email, '@') = 0;

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last'));

결과 해석:

설명: 버퍼 수는 그 SQL을 실행하려고 읽은 버퍼 개수이고, 작을수록 좋음
PostgreSQL에서는 POSITION('@' IN email) = 0과 EXPLAIN (ANALYZE, BUFFERS)를 썼음

#바꾸기와 채우기

SELECT ename, REPLACE(sal, 0, '*')
  FROM emp;

SELECT ename, LPAD(sal, 10, '*')
  FROM emp;

SELECT ename, REPLACE(sal::text, '0', '*') FROM emp;
SELECT ename, LPAD(sal::text, 10, '*') FROM emp;

결과 해석:

설명: Oracle은 숫자를 문자로 자동 변환하지만 PostgreSQL은 ::text로 직접 바꿔야 함
LPAD(sal, 10, '*')는 전체 10자리를 잡고 남는 왼쪽 공간을 *로 채움
숫자 0~3을 한꺼번에 바꾸는 REGEXP_REPLACE는 정규표현식에 있음

#잘라내기

SELECT 'smith', LTRIM('smith', 's'), RTRIM('smith', 'h'), TRIM('s' FROM 'smiths')
  FROM dual;

SELECT ename, sal
  FROM emp
 WHERE TRIM(ename) = 'JACK';

결과 해석:

실습에서는 이름 앞뒤에 공백이 있는 행(' JACK ')을 넣고 TRIM으로 찾았음
입력 문장은 실습 테이블을 바꿈

문제: 이메일 끝의 .com, .net을 잘라내기

SELECT ename, RTRIM(RTRIM(email, '.com'), '.net')
  FROM emp19
 WHERE email IS NOT NULL;

결과 해석: