[DB] 3. 함수 - 문자 조작 함수 (lower, initcap, upper, length, instr, substr, lpad, rpad, trim, replace)

함수

함수는 데이터 값을 조작하는데 사용한다.

중첩(nested)될 수 있다.

문자 함수/숫자 함수/날짜 함수/변환 함수 등이 있다.

 

문자 조작 함수

 

1️⃣ lower(소문자), initcpa(앞글자만 대문자), upper(대문자)

select 'abcDEF', lower('abcDEF'), upper('abcDEF'), initcap('abcDEF') from dual;

• 실제 테이블 조회시 아래와 같이 사용 가능하다.

select last_name, lower(last_name), upper(last_name), initcap(last_name) from employees;

• where조건절에 lower를 적용하여 소문자로 검색할 수 있다.

select * from employees where lower(last_name) = 'king';

컬럼에 값은 대소문자를 구분하기때문에 lower나 upper를 적용시켜 검색할 수 있다.

 

2️⃣ length(길이), instr(문자 찾기) - 없으면 0, 찾으면 index반환(index초기값이 1번부터 시작)

select first_name, length(first_name), instr(first_name, 'a') from employees;

3️⃣ substr(문자열 자르기) , concat(문자연결)

select 'abcdef', substr('abcdef', 3), substr('abcdef', 1, 3) from dual;

• 숫자 조건을 한개만 주면 그자리 까지 잘라서 그 이후를 보여주고, 두개의 숫자 조건을 주면 숫자~숫자 까지 잘라서 보여준다.

select 'abc' || 'def' from dual;

• 위와 같이 ||을 이용해서 문자열을 붙여줄 수 있지만 oracle에서만 지원되기 떄문에 다른 DB에서는 사용이 안될 수 있다.
• concat 함수를 사용하여 ||와 같이 문자열을 이어 붙여줄 수 있다.

select concat('abc', 'def') from dual;

• 실제로는 아래와 같이 사용할 수 있다. (성과 이름을 붙여서 출력하기)

select concat(first_name, last_name) as name from employees;

• 이름 사이에 공백을 주고 싶다면 어떻게 해야할까

-- ||를 이용해서 넣어줄 수도 있고.
select concat(first_name ||' ', last_name) as name from employees;

-- 함수의 중첩으로 공백을 넣어줄 수도 있다.
select concat(concat(first_name, ' '), last_name) as name from employees;

4️⃣ LPAD, RPAD

select LPAD('abc', 10, '*') from dual; -- 10자 중 abc를 제외한 글자는 왼쪽부터 *로 처리
select RPAD('abc', 10, '*') from dual; -- 10자 중 abc를 제외한 글자는 오른쪽부터 *로 처리

 

5️⃣ LTRIM, RTRIM, TRIM(공백제거)

-- LTRIM 
select LTRIM('     javascript_java') from dual; // 좌측 공백제거
select LTRIM('javascript_java', 'java') from dual; // 좌측제거

-- RTRIM 
select RTRIM('javascript_java     ') from dual; // 우측 공백제거
select RTRIM('javascript_java', 'java') from dual; // 우측제거

-- TRIM 
select trim('       java         ') from dual; //양쪽 공백(앞뒤로) 제거

 

6️⃣ Replace ⭐기억해두는 것이 좋다⭐

-- 수정할 문자와 어떤 문자로 수정할지 지정해서 문자를 변경해준다.
select replace ('my dream is a president','president','doctor') from dual; 
--같은 원리로 공백을 제거할 수 있다.
select replace ('my dream is a president',' ','') from dual; 

-- nested 중첩방식으로 두개를 한번에 처리 가능하다.
select replace(replace('my dream is a president','president','doctor'),' ', '') from dual;

 

연습문제

-- 연습문제


--문제 1.
--EMPLOYEES 테이블 에서 이름, 입사일자 컬럼으로 변경해서 이름순으로 오름차순 출력 합니다.
select first_name, hire_date from employees order by first_name asc;

--조건 1) 이름 컬럼은 first_name, last_name을 붙여서 출력합니다.
select first_name||' '||last_name, hire_date from employees order by first_name asc;

--조건 2) 입사일자 컬럼은 xx/xx/xx로 저장되어 있습니다. xxxxxx형태로 변경해서 출력합니다.
select first_name||' '||last_name, replace(hire_date,'/','') from employees order by first_name asc;

--문제 2.
--EMPLOYEES 테이블 에서 phone_numbe컬럼은 ###.###.####형태로 저장되어 있다
select phone_number from employees;

--여기서 처음 세 자리 숫자 대신 서울 지역변호 (02)를 붙여 전화 번호를 출력하도록 쿼리를 작성하세요
select replace(phone_number, substr(phone_number,1,3), '(02)') from employees;

--강사님
select concat('(02)', substr(phone_number, 4, length(phone_number))) as phone_number from employees;

--문제 3. 
--EMPLOYEES 테이블에서 JOB_ID가 it_prog인 사원의 이름(first_name)과 급여(salary)를 출력하세요.
select first_name, salary from employees where job_id = 'IT_PROG';

--조건 1) 비교하기 위한 값은 소문자로 입력해야 합니다.(힌트 : lower 이용)
select first_name, salary from employees where lower(job_id) = 'it_prog';

--조건 2) 이름은 앞 3문자까지 출력하고 나머지는 *로 출력합니다. 
--이 열의 열 별칭은 name입니다.(힌트 : rpad와 substr 또는 substr 그리고 length 이용)
select rpad(substr(first_name,1, 3), length(first_name), '*') as name, salary from employees where lower(job_id) = 'it_prog';

--조건 3) 급여는 전체 10자리로 출력하되 나머지 자리는 *로 출력합니다. 
--이 열의 열 별칭은 salary입니다.(힌트 : lpad 이용)
select rpad(substr(first_name,1, 3), length(first_name), '*')  as name, lpad(salary, 10, '*') as salary from employees where lower(job_id) = 'it_prog';

문제 1번 최종 결과값

문제 2번 최종 결과값

문제 3번 최종 결과값