Dual table을 활용하여 알아보는 SQL SELECT 함수

Dual table이란 가상의 테이블로 오라클에서 제공하는 간단한 테이블로, 함수 계산값을 바로바로 보여주는 미리보기 같은 존재입니다. 테이블창으로 이동하지 않더라도 함수를 사용한 간단한 식은 콘솔내에서 바로 확인할 수 있다는 장점이 있습니다. 다만 여러줄을 FROM DUAL;로 만들어놓고 블럭으로 실행은 불가능하고 한 줄씩만 볼 수 있습니다. 

 

1.CHR, ||, CONCAT

--문자함수
--CHR(숫자) : ASCII값으로 변환되어 넘어온다. -> 문자
SELECT CHR(65) FROM DUAL;
SELECT CHR(97) FROM DUAL;

--Strig str = "안녕" + "하세요"

SELECT '내 점수는 ' ||CHR(65)|| '입니다'FROM DUAL;

SELECT CONCAT ('안녕하세요 ', '반갑습니다')FROM DUAL;

CHR함수는 문자함수입니다. CHR(숫자) 를 넣으면 숫자에 맞는 ASCII값으로 변환되어서 넘어옵니다. 반대로 ASCII(문자)는 숫자로 바뀌겠죠? 첫번째 줄은 대문자 A가 될 것이고, 두번째 줄은 소문자 a가 될 것입니다.  문자열을 합치는 것은 어떻게 할까요? 자바에서는 + 로 붙였는데, SQL에서는 ||을 사용합니다. 자바에서는 OR이었는데 오라클로 넘어오니 + 의 기능을 하게 됐네요. 세번째 줄은 실행하면 내 점수는 A입니다 가 나오게 되겠죠!

 

더불어 문자열을 연결할 때 쓸 수 있는 함수가 또 하나 더 있는데요 바로 CONCAT입니다. Concatenate의 약자로 연결한다는 뜻인데요, 마지막줄처럼 CONCAT으로 연결을 하게 되면 안녕하세요 반갑습니다 가 출력이 됩니다. 

 

2. LPAD, RPAD

--LPAD(RPAD) : 나머지 빈 칸(지정문자)로 채운다. 

SELECT LPAD('BBB', 10) FROM DUAL;
SELECT RPAD('BBB', 10) FROM DUAL;
SELECT LPAD('BBB', 10, '-') FROM DUAL;
SELECT LPAD('BBB', 10, 0) FROM DUAL;

LPAD(Left-PAD), RPAD(Right-PAD)의 약자로 각 사이드의 자릿수를 채워주는 함수입니다. LPAD("값", "총 문자길이", "채움문자") 이런 구조인데요, 첫번째 LPAD('BBB', 10)의 경우 값을 'BBB'를 주고, 총 문자 길이는 10, 채움문자는 아무것도 주지 않아서 공백으로 남겨뒀네요. 그런 경우에는         BBB 이런식으로 만들어집니다. RPAD는 똑같이 방향만 반대로 BBB       이렇게 되겠죠? 세번째 줄의 경우에는 '-'으로 채우라고 지정했기 때문에 -------BBB가 되겠습니다. 마지막 줄은 0000000BBB가 될거에요. 

 

3.INSTR

--INSTR == indexOf('a') -> 'abcde' 자바는 0이겠지만 DB는 1부터 시작
SELECT INSTR('abcde', 'a') FROM DUAL; --1
SELECT INSTR('123ABC456DEFABC', 'A') FROM DUAL; --4
SELECT INSTR('123ABC456DEFABC', 'A',7) FROM DUAL; --13
SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 2) FROM DUAL; --16, 마지막 ABC의 A

자바의 String 클래스에서 indexOf를 이용해서 문자열의 위치를 확인했던 것처럼 SQL에도 같은 기능을 하는 함수가 있습니다 바로 INSTR인데요 , 문자열 내에서 문자의 위치를 알려줍니다. 다만 주의해야 할 점은 자바에서는 0부터 시작했다면 SQL에서는 1부터 시작한다는 점입니다. 그러므로 첫번째 줄의 결과값은 1이 되겠죠. 

INSTR( 문자열, 찾고자 하는 문자 값, 찾기 시작 위치, 찾은 결과 중 몇번째 값) 이러한 구조로 이뤄집니다. 

모든 기능을 담고 있는 마지막 줄로 확인해보도록 하겠습니다.

                7 8 9 10 11 12 13 14 15 16 17 18

1 2 3 A B C 4 5 6 D   E   F  A   B   C   A   B   C

 

7번째부터 찾기를 시작하기로 했으므로 4가 시작점입니다. 여기서 부터 A를 찾는데요, 문제는 A가 2번 나온다는 것입니다. 그렇다면 첫번째로 나온 A를 구하라는건지 아니면 두번째로 나온 A를 구하라는 것인지 확실하게 알아야겠죠? 마지막에 2가 힌트가 되네요, 그렇다면 결과값은 16이 됩니다. 

 

4.REPLACE

SELECT REPLACE('AAAAABCD', 'A') FROM DUAL; -- 바꿀 글자 지정 안 하면 A가 사라지고 BCD됨
SELECT REPLACE('AAAAABCD', 'A', 'a') FROM DUAL;--aaaaaBCD
SELECT REPLACE('AAAAABCD', 'AA','a') FROM DUAL;--aaABCD

문자열 내에서 특정 문자를 바꾸는 함수도 있습니다. REPLACE함수를 쓰면 바꾸고자 하는 값을 아예 없애주거나 내가 원하는 값으로 바꿀 수 있습니다. 첫번째 줄처럼 글자를 지정하지 않으면 그냥 A가 사라지고 BCD만 남게 됩니다. 그러나 두번째 줄처럼 대문자 A를 소문자 a로 바꿀수도 있습니다. 만약 바꾸고자 하는 글자가 하나가 아니라 문자열이라면 어떻게 될까요? 그렇다면 두개를 하나로 보고 앞에 4개는 두개씩 묶여서 aa로 바뀌지만 마지막 A 하나는 외톨이이기 때문에 replace되지 않고 고대로 대문자 A로 남게 됩니다. 

 

5.TRANSLATE

--TRANSLATE : 문자 치환
SELECT TRANSLATE('AAAAABCD', 'A', 'a') FROM DUAL; -- TRANSLATE는 무조건 바꿀 문자를 지정해줘야함 
SELECT TRANSLATE('AAAAABCD', 'AA', 'a') FROM DUAL;--인식을 한 글자만 함

앞의 REPLACE와 비슷하지만 다른 점이 두개 있는데 첫번째는 바로 앞에서는 그냥 바꿀 문자를 정해주지 않아도 알아서 없애버렸던 반면 TRANSLATE에서는 무조건 어떻게 바꿀 것인지를 알려줘야 합니다. 그래야 첫번째 줄 같은 경우 aaaaaBCD로 바뀌게 됩니다. 두번째 줄의 경우에는 REPLACE처럼 aaABCD 로 바꿔줄 줄 알았는데 그렇지 않고 한글자씩만 인식을 하기 때문에 이것도 마찬가지로 aaaaaBCD가 나오게 됩니다. 그러므로 문자열 내에서 여러 문자를 바꿔야 하는 경우에는 TRANSLATE가 아닌 REPLACE를 써야겠습니다. 

 

6.SUBSTR

--JAVA SUBSTRING "ABCDE" -> substring(1,3) -> "BC"
--SUBSTR
SELECT SUBSTR('ABCDE', 3) FROM DUAL; --3번지부터 끝까지 출력 CDE
SELECT SUBSTR('ABCDE', 3,2) FROM DUAL; -- 3번지부터 시작해서 2글자 가져와 CD

자바에서 substring 함수는 시작위치에서 끝 위치에서 1 뺀 값까지 출력해줬습니다. SQL의 SUBSTR은 조금 다른데요, 일단 첫번째 줄을 보면 ('ABCDE', 3) 이렇게 되어있는 걸 확인할 수 있는데 이는 시작하는 위치가 3이고 끝까지 출력하라는 뜻입니다. 결과값은 CDE가 나옵니다. 두번째는 3번째에서 시작해서 2글자를 가져오라는 뜻으로 CD가 출력됩니다. 자바였다면 substring(2,4)였겠죠?? 그러나 SQL은 보다 더 직관적으로 정말 눈에 보이는 숫자 그대로 뽑아오게 됩니다. 

 

7. CEIL, FLOOR

--올림

SELECT CEIL (13.1) FROM DUAL; --ceiling

--내림

SELECT FLOOR(13.9) FROM DUAL;--floor

CEIL은 소수점 자리수를 올려주고, FLOOR는 내려줍니다. 그럼 각각의 값은 14, 13이 되겠죠. CEIL은 ceiling(천장)의 약자이고 FLOOR는 바닥이라는 뜻이니 올리고 내리고를 제대로 기억할 수 있겠죠?

 

8. MOD, POWER, ROUND

--나눈 나머지

SELECT MOD(3,2) FROM DUAL; --결과값은 나머지인 1

--제곱

SELECT POWER(3,2) FROM DUAL; --3의 2승

--반올림

SELECT ROUND(13.5) FROM DUAL; --결과값 14
SELECT ROUND(13.4) FROM DUAL; --결과값 13

자바에서 나머지를 %을 사용해서 구했다면 SQL에서는 MOD함수를 사용합니다. MOD(3,2)는 3을 2로 나눈 나머지인 1을 결과값으로 냅니다. POWER(3,2)는 3의 2승인데 즉, 제곱함수이므로 9가 출력됩니다.  ROUND는 반올림으로 앞서 CEIL과 FLOOR가 각각 올리고 내리는 역할이 달랐던 것과는 다르게 ROUND함수 하나로도 반올림을 할 수 있습니다. 

 

9. SIGN, TRUNC

--부호 / +이면 1, 0이면 0, -이면 -1

SELECT SIGN(13.4) FROM DUAL; --결과값 1
SELECT SIGN(0) FROM DUAL;--결과값 0
SELECT SIGN(-13.4) FROM DUAL;--결과값 -1

--TRUNC 버림
SELECT TRUNC(123.456) FROM DUAL; -- 결과값 123
SELECT TRUNC(123.456,2) FROM DUAL; --결과값 123.45
SELECT TRUNC(123.456,1) FROM DUAL;--결과값 123.4
SELECT TRUNC(123.456,-1) FROM DUAL; --결과값 120

SIGN함수는 숫자가 양수인지, 0인지, 음수인지를 알려줍니다.  양수일 경우에는 1이 나오고, 0일 경우에는 똑같이 0, 마지막으로 음수일 경우에는 -1을 출력해줍니다. TRUNC함수는 소수점 자리수를 버리는 것인데요, 앞서 반올림과는 다르게 반올림 하지 않고 그냥 다 쿨하게 버려버립니다. 하나씩 살펴보겠습니다.

 

1. TRUNC(123.456) -> 소수점 세 자리를 버리므로 결과값은 123입니다.

2. TRUNC(123.456,2) -> 다 버리진 않고 두 자리는 남겨두라네요? 123.45입니다.

3. TRUNC(123.456,1) -> 더 나아가 이번에는 한 자리만 남겨두겠습니다. 123.4입니다.

4. TRUNC(123.456, -2) -> 어라 이번에는 음수가 나왔습니다, 앞에서 1이 나왔을 때는 소수점 한 자리를 남겨두는 것이었는데 이번에는 뭘 하라는 걸까요? 바로 정수 자리까지 올라가서 소수점을 기준으로 해당 자리 이하는 버리라는 것입니다. 123.456에서 소수점을 기준으로 봤을 때 한 칸 뒤로 간 것은 3이 되겠죠. 3을 버리고 즉 123.456   이 부분을 다 지우고, 120만 남게 되는 것입니다. 만약 버릴 자릿수가 -2이라면 100이 되겠죠?

 

10. TO_CHAR

--TO_CHAR
--DATE -> VARCHAR2

SELECT TO_CHAR(SYSDATE) FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH-MI-SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(10000000,'$999,999,999')FROM DUAL;

TO_CHAR함수는 DATE 자료형을 VARCHAR2로 바꿔줍니다. 첫번째 줄처럼 현재 시간을 VARCHAR2형으로 기본적으로 바꿀수도 있고, 두번째 세번째처럼 내가 원하는 형식으로 만들어줄수도 있습니다.  돈 같은 경우에도 그냥 숫자를 입력한 다음에 달러화를 표현하는 방식으로 고쳐줄수도 있습니다. 원화 같은 경우에는 마찬가지로 형식에 '\999,999,999'를 적으면 되겠죠 다만 주의할 점은 9가 아닌 다른 숫자를 적으면 안 된다는 점입니다. 

 

9를 1로만 바꿨을 뿐인데 에러가 뜹니다. invallid number format model parameter를 넣었다고 혼내네요..해결책으로는 매뉴얼을 읽어보랍니다. 참나! 그래서 확인해봤습니다 왜 9가 아니면 안 되는건지?

 

기본적으로 TO_CHAR함수에서 0과 9는 각각 가지고 있는 역할이 있습니다. 9는 숫자를 표시하되, 값이 없는 경우에는 null로 처리를 해버리고, 반대로 0은 숫자를 표시하되 값이 없는 경우에는 0으로 처리를 해버립니다. 따라서 만약 '$000,000,000'으로 했을 경우에는 우리가 원하는 건 천만달러인데 '$010,000,000'이라는 요상한 형태로 나와버리는것이죠. 따라서 예쁘게 출력하기 위해서는 반드시! 9를 사용하여야겠습니다. 

 

11.TO_DATE

--TO_DATE
--VARCHAR2 -> DATE

SELECT TO_DATE('20210525') FROM DUAL; --결과값 21/05/25
SELECT TO_DATE('20210525','YYYYMMDD') FROM DUAL; --결과값 21/05/25

이번에는 반대로 VARCHAR2형식을 DATE형식으로 바꿔주는 것입니다. 그냥 숫자로 이뤄진 문자열을 넣었을 뿐인데 날짜처럼 21/05/25가 나옵니다. 두번째줄처럼 형식을 직접 넣어줄수도 있습니다. 그러나 안 넣어줬을때랑 결과는 동일합니다. 주의할 점은, 나라마다 날짜를 표기하는 방식이 다른 곳도 있다는 것인데,, 예를 들어 미국의 경우 month, day, year 순으로 날짜를 표기합니다. 그렇다고 해서 TO_DATE('05252021', 'MMDDYYYY') 하면 05/25/21이 출력될까요? 정답은 NO입니다.  이것도 역시나 21/05/25가 나옵니다. 그러므로 그냥 YYYYMMDD 형식으로만 출력된다고 생각하면 되겠습니다. 

 

12.TO_NUMBER

--TO_NUMBER 
--VARCHAR2 -> NUMBER

SELECT TO_NUMBER('123') + 45 FROM DUAL; --결과값 168

앞선 포스팅을 보시면 NUMBER 자료형에 작은따옴표를 붙인 값도 그냥 들어간다는 것을 확인한 적이 있습니다. 이번에는 그러한 경우는 아니지만 일단 문자열로 삽입하여 숫자로 바꿔주는 TO_NUMBER함수입니다. 문자열 '123'은 TO_NUMBER 함수를 만나 numeric value가 되고 168이라는 결과값을 내줍니다. 

 

13.LAST_DAY

--LAST_DAY
SELECT LAST_DAY('21/12/02') FROM DUAL; -- 결과값 21/12/31

--예제
SELECT LAST_DAY(TO_DATE('210301', 'YYMMDD'))FROM DUAL; -- 결과값 21/03/31

의도한 건 아니지만 마지막으로 마지막날을 계산하는 LAST_DAY함수이네요, 이름에서도 알 수 있다시피 우리가 랜덤한 날짜를 입력하면 해당하는 월의 마지막 날을 출력해줍니다. 친절하죠? 밑에 예제처럼 일단 문자열을 먼저 DATE형으로 만들고 그렇게 21/03/01의 마지막 날인 21/03/31을 출력하는 방식도 사용해볼 수 있겠습니다.