[Database] 12장 함수 모음1

 

문자열 변경 (translate)

 

-- translate
-- c2의 문자열을 c3으로 변경하는데 c1의 양식에 맞춰 변경함.
select translate(c1, c2, c3) from table

 

-- translate
select translate('2009-02-28', '0123456789-', 'ABCDEFGHIJK') result from dual;

 

결과 화면1

 

 

-- 회원 테이블의 회원 아이디에서 숫자를 제거하여 출력하시오.
select mem_id mem_id_befor, translate(mem_id, '0123456789-', ' ') mem_id_after from member;

 

결과 화면2

 

 

 

문자열 자르기 (replace)

 

-- replace
-- c1의 문자열 중 c2의 문자열을 c3으로 변경함.
select replace(c1, c2, c3) from table

 

-- replace
select replace('SQL Project', 'SQL', 'SSQQLL') as 문자치환1,
    replace('Java Flex Via', 'a') as 문자치환2
    from dual;

 

결과 화면3

 

 

-- '삼' -> '육'
select buyer_name, replace(buyer_name, '삼', '육')
    from buyer;

 

결과 화면4

 

 

-- 회원테이블의 회원성명 중 '이' -> '리' 로 치환 검색하시오.
select mem_name, replace(mem_name, '이', '리') name from member;

 

결과 화면5

 

 

 

문자 위치 리턴 (instr)

 

-- instr
-- c1의 문자열 중에서 c2의 문자 위치를 리턴함.
-- select instr(c1, c2, m) : c1의 문자열 중에서 c2의 문자 위치를 리턴하는데 m번째 위치부터 찾아봄.
select instr(c1, c2) from table;

 

-- instr
select instr('hello heidi', 'he') as result1,
    instr('hello heidi', 'he', 3) as result2
    from dual;

 

결과 화면6

 

 

 

문자열 길이 (length, lengthb)

 

-- length : 문자열의 길이
-- lengthb : 문자열의 바이트 길이
select length(문자열) from table;
select lengthb(문자열) from table;

 

-- length
-- lengthb
select length('SQL 프로젝트') "LENGTH",
    lengthb('SQL 프로젝트') "LENGTHB"
    from dual;

 

결과 화면7

 

 

 

절대값, 양수 & 음수, n승, 제곱근

 

-- abs(n) : 절대값
select abs(n) from table;

-- sign(n) : 양수, 음수
select sign(n) from table;

-- power(n, y) : n의 y승
select power(n, y) from table;

-- sqrt(n) : n의 제곱근
select sqrt(n) from table;

 

 

1. 절대값

-- abs(n) 절대값
select abs(-365) from dual;

 

결과 화면8

 

 

2. 양수 & 음수

-- sign(n) 양수, 음수
select sign(12), sign(0), sign(-55)
    from dual;

 

결과 화면9

 

 

3. n승

-- power(n, y) n의 y승
select power(3, 2), power(2, 10)
    from dual;

 

결과 화면10

 

 

4. 제곱근

-- sqrt(n) : n의 제곱근
select sqrt(2), sqrt(9) from dual;

 

결과 화면11

 

 

 

큰 값 또는 작은 값 리턴

 

-- greatest(c1, c2, c3) : c1, c2, c3 중 큰 값 리턴
select greatest(c1, c2, c3) from table;

-- least(c1, c2, c3) : c1, c2, c3 중 작은 값 리턴
select least(c1, c2, c3) from table;

 

-- greatest
-- least
select greatest(10, 20, 30) "큰값",
    least(10, 20, 30) "작은값"
    from dual;

 

결과 화면12

 

 

-- greatest
-- least
select greatest('강아지', 256, '송아지') "큰값",
    least('강아지', 256, '송아지') "작은값"
    from dual;

 

결과 화면13

 

 

 

아스키값 확인 (ascii)

 

-- ascii(c1) : c1의 ascii 코드의 값을 출력
-- 첫글자만 가지고 아스키값을 구함.
select ascii(c1) from table;

 

-- ascii 의 경우 첫글자만 가지고 아스키값을 구함.
select ascii('강아지') 강아지ascii,
    ascii('강') 강ascii,
    ascii('아지') 아지ascii,
    ascii('송아지') 송아지ascii
    from dual;

 

결과 화면14

 

 

 

수학 관련 함수 (floor, ceil, remainder, mod)

 

-- floor(n) : n과 같거나 작은 수
select floor(n) from table;

-- ceil(n) : n과 같거나 큰 수
select ceil(n) from table;

-- remainder(c, n) : n으로 나눈 나머지 (mod 함수와 유사)
-- 소수 첫째자리에서 반올림
select remainder(c, n) from table;

-- mod(c) : 소수 첫째자리에서 버림
select mod(n) from table;

 

 

1. floor, ceil 예시

select floor(1332.69), ceil(1332.69) from dual;

 

결과 화면15

 

select floor(-1332.69), ceil(-1332.69) from dual;

 

결과 화면16

 

 

2. remainder, mod 예시

select mod(10, 3), remainder(10, 3) from dual;

 

결과 화면17

 

select mod(10, 3.7), remainder(10, 3.7) from dual;

 

결과 화면18

 

 

 

구간 확인 (width_bucket)

 

-- width_bucket(c, min, max, b) : min과 max 범위 중에 b 구간으로 나눈 후 c가 어디에 속하는지 확인
select width_bucket(c, min, max, b) from table;

 

select width_bucket(88, 0, 100, 10) from dual;

 

결과 화면19

 

-- 회원 테이블에서 회원이름, 마일리지, 등급을 출력하시오.
-- 단, 등급은 마일리지를 500부터 9000까지 5등급으로 한다.
select mem_name 회원이름, mem_mileage 마일리지, width_bucket(mem_mileage, 9000, 500, 5) 등급 
    from member
    order by 등급;

 

결과 화면20

 

 

 

숫자의 절사 (round)

 

-- 0.5를 뺀 후 반올림 => 버림
select round(345.123 - 0.05, 1) "RESULT1", -- 소수점 둘째자리 버림
    round(345.123 - 0.5, 0) "RESULT2", -- 소수점 첫째자리 버림
    round(345.123 -5, -1) "RESULT3" -- 첫째자리 버림
    from dual;

 

결과 화면21

 

 

-- 100/9 결과를 십의 자리까지 나타낸 후 일의 자리 버림
select round(100/9 -5, -1) result from dual;

 

결과 화면22

 

 

• round 함수 활용

select 10000/7 from dual;

 

결과 화면23

 

select floor(10000/7 * 1000) / 1000 from dual; -- 소수점 셋째자리

 

결과 화면24

 

select floor(10000/7 * 100) /100 from dual; -- 소수점 둘째자리

 

결과 화면25

 

select floor(10000/7 * 10) / 10 from dual; -- 소수점 첫째자리

 

결과 화면26

 

select floor(10000/7) from dual; -- 자연수

 

결과 화면27

 

select floor(10000/7 / 10) * 10 from dual; -- 첫째자리

 

결과 화면28

 

select floor(10000/7 / 100) * 100 from dual; -- 둘째자리

 

결과 화면29

 

select floor(10000/7 / 1000) * 1000 from dual; -- 셋째자리

 

결과 화면30

 

 

 

문자열로 변환 (to_char)

 

-- to_char(c1, c2) : c1을 c2의 형태인 문자열로 변환
select to_char(c1, c2) from table;

 

-- .00으로 인해 자리수를 맞춰줌
select to_char(1234.6, '99,999.00') from dual;

 

결과 화면31

 

-- l의 경우 원화를 보여줌, pr의 경우 음수일 때 <>를 추가해줌
select to_char(-1234.6, 'l9999.00pr') from dual;

 

결과 화면32

 

-- xxx의 경우 16진수로 나타냄
select to_char(255, 'xxx') from dual;

 

결과 화면33

 

 

 

숫자형으로 변환 (to_number)

 

-- to_number(c) : 숫자형식인 c의 문자열을 숫자로 변환
-- 원화 표시의 경우 to_number(c, c1) : c1에 L999,999 식으로 변경 후 변환하여야함.
select to_number(c) from table;

 

select to_number('3.1415')
    from dual;

 

결과 화면34

 

select to_number('1200')
    from dual;

 

결과 화면35

 

-- 원화 표시가 있을 때 L을 사용하여 원화 표시를 변경 후 변경해줘야 함.
select to_number('￦3,200', 'L999,999')
    from dual;

 

결과 화면36

 

 

• 응용 (substr, lpad, to_char, replace)

select mem_id 회원id,
    substr(mem_id, 1, 1) 조합회원id, -- 첫번째 자리부터 1자리만
    substr(mem_id, 2), -- 두번째부터 나머지 다 출력
    to_char(substr(mem_id, 2) + 10, '000'), -- 001에 10을 더한 후(11) 000의 모양의 char로 변경
    lpad(substr(mem_id, 2) + 10, 3, '0'), -- 001에 10을 더한 후(11) 세자리의 남은 자리수의 왼쪽에 0을 추가함
    substr( (1000+to_number(substr(mem_id, 2))+10), 2 ), -- 001에 숫자로 변경 후 1000과 10을 더한 후(1011) 둘째 자리부터 끝까지 자름 
    replace(substr(mem_id, 2) + 10, '11', '011') -- 001에 10을 더한 후(11) 11을 011로 변경
    from member
    where mem_name = '이쁜이';

 

결과 화면37

 

 

 

date형으로 변환 (to_date)

 

확인하기 위해선 보이는 날짜 형식을 변경하여야 한다.

하단의 날짜 형식 을 변경하여 날짜 형식을 YYYY/MM/DD HH24:MI 을 변경하였다.

 

https://forest-of-coding.tistory.com/44

[Database] 11장 날짜 타입

 

 

-- to_date(c) : c를 데이터 형식으로 변환
-- to_date(c, c1) : c1의 형식으로 c를 데이터로 변환
select to_date(c) from table;

 

select to_date('2023-12-01') + 3
    from dual;

 

결과 화면38

 

select to_date('202312221210', 'YYYYMMDDHH24MI')
    from dual;

 

결과 화면39

 

 

 

그룹 (group by)

 

-- group by
select prod_buyer 거래처,
    prod_lgu 상품분류,
    max(prod_sale) 최고판매가,
    min(prod_sale) 최소판매가,
    count(prod_sale) 자료수
    from prod
    group by prod_buyer, prod_lgu
    order by prod_buyer, prod_lgu;

 

결과 화면40

 

 

 

그룹간의 합계 (rollup, cube)

 

1. rollup

-- rollup : 그룹간의 합계 계산
select prod_lgu,
    prod_buyer,
    count(*),
    sum(prod_cost)
    from prod
    group by prod_lgu, rollup(prod_buyer);

 

결과 화면41

 

-- rollup : 그룹간의 합계 계산
select prod_lgu,
    prod_buyer,
    count(*),
    sum(prod_cost)
    from prod
    group by rollup(prod_lgu, prod_buyer);

 

결과 화면 42

 

 

2. cube

 

: rollup과 달리 전체 합계도 구해줌

 

-- cube
select prod_lgu,
    prod_buyer,
    count(*),
    sum(prod_cost)
    from prod
    group by cube(prod_lgu, prod_buyer);

 

결과 화면43

 

 

 

null 관련 (is null, is not null, nvl, coalesce)

 

1. is null : null인 걸 찾음

2. is not null : null이 아닌 걸 찾음

3. nvl(c, r) : c의 값이 null이면 r로 대체

4. nvl2(c, r1, r2) : c의 값이 null이 아니면 r1, null이면 r2

5. coalesce(p) : 파라미터 중 null이 아닌 첫 번째 파라미터 반환

 

 

-- null에는 더하든 곱하든 다 null임
-- 0으로 바꾸면 더한 값이 제대로 들어감
select null+10 덧셈, 10*null 곱셈 from dual;
select nvl(null, 0)+10 덧셈, nvl(null, 0)*10 곱셈 from dual;

 

결과 화면44

 

결과 화면45

 

 

 

Regular Expression

 

1. regexp_like

: 문자열이 있는지 확인

-- regexp_like : 문자열 있는지
-- 김으로 시작하고 성이나 형이 들어간 이름을 검색
select mem_id 회원id,
    mem_name 회원명
    from member
    where regexp_like(mem_name, '^김(성|형)');

 

결과 화면46

 

 

2. regexp_substr

: 일치하는 문자열 반환

-- regexp_substr : 문자열 일치 하위 문자열 반환
-- 공백이 아닌 데이터 중 첫번째 문자열을 반환
select regexp_substr('Java Flex Oracle', '[^ ]+')
    from dual;

 

결과 화면47

 

-- ^ : 아닌 것을
-- 문자열을 첫번째부터 시작해서 세번째 문자열을 반환하는데 공백이 아닌 데이터를 반환
select regexp_substr('Java Flex Oracle', '[^ ]+', 1, 3)
    from dual;

 

결과 화면48

 

select mem_name 회원이름,
    mem_mail 이메일,
    regexp_substr(mem_mail, '[^@]+') 이메일아이디,
    regexp_substr(mem_mail, '[^@]+', 1, 2) 이메일서버
    from member;

 

결과 화면49

 

 

select regexp_substr('c-01-02', '[^-]+', 1, 1) from dual;

 

결과 화면50

 

 

select regexp_substr('c-01-02', '[^-]+', 1, 2) from dual;

 

결과 화면51

 

 

select regexp_substr('c-01-02', '[^-]+', 1, 3) from dual;

 

결과 화면52

 

 

3. regexp_replace

: 문자열을 다른 문자로 반환

-- regexp_replace : 문자열을 다른 문자로 반환
-- 어디서부터 할 것인지 지정하지 않을 시 전체 변경
select regexp_replace('Java Flex Oracle', '[^ ]+', 'C++') from dual;

 

결과 화면53

 

 

-- 첫번째부터 시작해서 두번째꺼를 변경
select regexp_replace('Java Flex Oracle', '[^ ]+', 'C++', 1, 2) from dual;

 

결과 화면54

 

 

4. regexp_instr

: 해당되는 위치를 숫자로 나타냄.

-- regexp_instr : 해당되는 위치를 숫자로 나타냄
-- regexp_instr(해당 문자열, 정규식패턴, 시작위치, 일치횟수, 매칭 파라미터)
-- a 또는 e가 있는 첫번째 위치
select regexp_instr('JAVA Flex Oracle', '[ae]') result from dual;

 

결과 화면55

 

 

-- a 또는 e가 오는데 첫번째부터 시작하여 첫번째에 해당되는 위치(0)를 대문자소문자(i) 구별하지 않고 찾음
-- a 또는 e가 오는데 세번째부터 시작하여(v부터 시작) 두번째에 해당되는 다음 위치(1)를 대문자소문자(i) 구별하지 않고 찾음
select regexp_instr('JAVA Flex Oracle', '[ae]', 1, 1, 0, 'i') result1,
    regexp_instr('JAVA Flex Oracle', '[ae]', 3, 2, 1, 'i') result2 -- x 위치를 나타냄.
    from dual;

 

결과 화면56

 

 

-- 소문자 대문자 없이 v부터 찾아 세번째 a 또는 e가 있는 위치를 찾는다. Oracle의 a이지만 1로 인해 그 다음 숫자인 c의 위치를 반환함.
select regexp_instr('JAVA Flex Oracle', '[ae]', 1, 1, 0, 'i') result1,
    regexp_instr('JAVA Flex Oracle', '[ae]', 3, 3, 1, 'i') result2
    from dual;

 

결과 화면57

 

 

-- 상품명 중 숫자가 없는 것들이 0 => regexp_instr(prod_name, '\d') = 0
select prod_id 상품코드, prod_name 상품명, prod_sale 판매가, regexp_instr(prod_name, '\d') 
    from prod
    where regexp_instr(prod_name, '\d') = 0;
-- 0을 9로 변경 시 숫자가 9번째에 있는 상품 조회

 

결과 화면58