[Oracle] 단일행 함수 - 문자함수
함수
-중첩가능
1. 단일행 함수
①문자함수
upper : 대문자로 변환
lower : 소문자로 변환
initcap : 첫글자 대문자 나머지는 소문자로 변환
-인덱스를 변형하기 때문에 주의
문자 연결(concat)
concat : 연결연산자(||)와 동일한 기능의 함수. 2개만 연결 가능. 리터럴문자 불가! 열이름만 가능!!
열이름1 || 열이름2 || 열이름3 = concat(concat(열이름1,열이름2),열이름3)
문자의 길이(length,lengthb)
length : 문자의 길이를 숫자값으로 반환
lengthb : 문자의 바이트값으로 반환(영문자)
from dual; : 가상의 테이블 환경을 구성하여 결과 확인할 때 사용.
select * from nls_session_parameters; --session(지역)의 정보(client 정보)
select * from nls_database_parameters; --database 정보(db server 정보)
- AL32UTF8(유니코드) : db에 설정된 문자, 출판되는 글자는 다 입력가능. 한글 3byte
- KO16MSWIN949 : 한글,한자,영어,일어만 저장. 한글 2byte
instr(열이름 또는 ‘문자열’,’찾는 문자’,시작점,몇번째로 찾는지)생략가능
: 문자의 위치를 리턴하는 함수. 위치 기준은 시작점이 바뀌어도 똑같음!!
instr(열이름,’a’,1,1) => ’a’가 처음으로 나오는 위치
instr(열이름,’a’,1,2) => ’a’가 두번째로 나오는 위치
[문제13]문자열 중에 두 번 이상 a가 있는 last_name을 출력
①
select * from employees
where instr(last_name,'a',1,2)>0;
②
select * from employees
where instr(last_name,’a’,1,2)>0;
③
select * from employees
where last_name like ‘%a%a%’;substr(열이름 또는 '문자',시작점,추출개수)
: 문자를 개수만큼 추출하는 함수
substr(열이름,1,1) —첫번째 글자 추출
substr(열이름,-1,1) —마지막 글자 추출
-substrb(열이름 또는 '문자',시작점,추출 바이트수) : 문자를 바이트 수만큼 추출하는 함수
한글 : 3byte, 영문 : 1byte (유니코드)
trim(’제거할 문자열’ from 열이름 또는 ‘문자열’)
: 왼쪽(접두), 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
-trim(열이름 또는 ’문자열’) : 접두/접미 공백문자 제거 시 from 사용 X
-ltrim(열이름 또는 ‘문자열’,’제거할 문자’) : 왼쪽(접두)부분에 연속되는 문자 제거
-rtrim(열이름 또는 ‘문자열’,’제거할 문자’) : 오른쪽(접미) 부분에 연속되는 문자를 제거하는 함수
[문제14] employees 테이블에 last_name 컬럼의 값 중에 "J" 또는 "A" 또는 "M"으로 시작하는 사원들의 last_name(첫번째 문자는 대문자, 나머지는 모두 소문자)과 last_name의 길이를 표시하는 query(select문) 를 작성합니다. 사원들의 last_name을 기준으로 결과를 오름차순 정렬해 주세요.
①
select initcap(last_name), length(last_name)
from employees
where instr(last_name,'J',1,1)=1
or instr(last_name,'A',1,1)=1
or instr(last_name,'M',1,1)=1
order by last_name asc;
②
select initcap(last_name), length(last_name)
from employees
where last_name like 'J%'
or last_name like 'A%'
or last_name like 'M%'
order by last_name asc;
③
select initcap(last_name), length(last_name)
from employees
where substr(last_name,1,1) in (’J’,’A’,’M’)
order by last_name asc;[문제15] employees테이블에서 department_id(부서코드)가 50번 사원들 중에 last_name에 두번째 위치에 "a"글자가 있는 사원들을 조회하세요.
①
select * from employees
where department_id=50
and instr(last_name,'a',1,1)=2;
②
select * from employees
where department_id=50
and last_name like '_a%';
③
select * from employees
where department_id=50
and substr(last_name,2,1)='a';replace(열이름 또는 '문자','old 문자','new 문자')
: 문자를 다른 문자로 치환하는 함수
-new문자를 입력하지 않으면 빈 문자열(’’)이 기본값으로 입력된다.
특정 철자를 N개 만큼 채우기(lpad,rpad)
lpad(열이름 또는 ‘문자열’,전체길이,'채워넣을 문자열')
: 문자의 자리를 고정시킨 후 문자값을 오른쪽 기준으로 채우고
빈 왼쪽 공백을 다른값으로 채움
rpad(열이름 또는 ‘문자열’,전체길이,'채워넣을 문자열')
: 문자의 자리를 고정시킨 후 문자값을 왼쪽 기준으로 채우고
빈 오른쪽 공백을 다른값으로 채움
[문제16] salary에 있는 값을 1000당 * 출력
SALARY STAR
6000 ******
4800 ****
4800 ****
...
select salary, lpad('',salary/1000,'') as "STAR"
from employees;[문제20] 주민번호 뒷6자리부분을 아래화면과 같이 출력해주세요.
주민번호_1 주민번호_2
210101-1234567 210101-1******
select '210101-1234567' as 주민번호_1, rpad(substr('210101-1234567',1,8),14,'*') as 주민번호_2
from dual;[문제21] 전화번호 가운데 4자리부분을 아래화면과 같이 출력해주세요.
전화번호_1 전화번호_2
010-1000-1004 010-*****-1004
select '010-1000-1004' as 전화번호_1, rpad(substr('010-1000-1004',1,4),8,'*') ||
substr('010-1000-1004',9,5) as 전화번호_2
from dual;[문제22] 메일주소를 아래화면과 같이 출력해주세요.
EMAIL_1 EMAIL_2
james@it,com j*@it,com
①
select 'james@it,com' as "EMAIL_1", concat(concat(substr('james@it,com',1,1) ,
rpad('',instr('james@it,com','@',1,1)-2,'')),
substr('james@itwill.com',instr('james@it,com','@',1,1)))
as "EMAIL_2"
from dual;
②
select 'james@it,com' as "EMAIL_1", rpad(substr('james@it,com',1,1),
instr('james@it,com','@',1,1)-1,'*') ||
substr('james@it,com',
instr('james@it,com','@',1,1))
as "EMAIL_2"
from dual;