728x90
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | -- Standard Function -- DUAL TABLE : ๊ฐ์ํ
์ด๋ธ -> ๊ฒฐ๊ณผ ํ์ธ์ฉ ํ
์ด๋ธ SELECT 1 FROM DUAL; SELECT 'A' FROM DUAL; SELECT '๊ฐ' FROM DUAL; SELECT 23 * 34 FROM DUAL; -- ๋ฌธ์ ํจ์ -- CHR( N ) : ASCII ๊ฐ์ ๋ฌธ์๋ก ๋ณํ SELECT CHR(65) FROM DUAL; SELECT CHR(97) FROM DUAL; -- String str = "์๋
ํ์ธ์ " + "๊ฑด๊ฐํ์ธ์" -- String str = "์๋
ํ์ธ์ " || "๊ฑด๊ฐํ์ธ์" SELECT '๋ด ์ ์๋ ' || CHR(65) || ' ์
๋๋ค' FROM DUAL; -- CONCAT -- LPAD(RPAD) : ๋๋จธ์ง๋ฅผ ๋น์นธ(์ง์ ๋ฌธ์)์ผ๋ก ์ฑ์ด๋ค SELECT LPAD('BBB', 10) FROM DUAL; SELECT RPAD('BBB', 10) FROM DUAL; SELECT LPAD('BBB', 10, '-') FROM DUAL; SELECT LPAD('123', 10, '0') FROM DUAL; -- INSTR == indexOf('a') -> "abcde" -> 0 SELECT INSTR('123ABC456DEF', 'A') FROM DUAL; SELECT INSTR('123ABC456DEFABC', 'A', 7) FROM DUAL; SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 1) FROM DUAL; SELECT INSTR('123ABC456DEFABCABC', 'A', 7, 2) FROM DUAL; SELECT INSTR('123ABC456DEF', 'y') FROM DUAL; --REPLACE : ๋ฌธ์์ด ์นํ SELECT REPLACE('AAAAACD', 'A') FROM DUAL; SELECT REPLACE('AAAAACD', 'A', 'a') FROM DUAL; SELECT REPLACE('AAAAACD', 'AA', 'a') FROM DUAL; -- TRANSLATE : ๋ฌธ์ ์นํ SELECT TRANSLATE('AAAAABCD', 'A', 'a') FROM DUAL; SELECT TRANSLATE('AAAAABCD', 'AA', 'a') FROM DUAL; -- SUBSTR : ์๋ฐ(substring(1,3) "ABCDE" -> "BC" SELECT SUBSTR('ABCDE', 3) FROM DUAL; -- 3๋ฒ์ง๋ถํฐ ์ถ๋ ฅํด๋ผ(0๋ถํฐ ์ธ๋ ์๋ฐ์ ๋ฌ๋ฆฌ 1๋ถํฐ ์
) -> CDE SELECT SUBSTR('ABCDE', 3, 2) FROM DUAL; -- 3๋ฒ์ง๋ถํฐ 2๋ฌธ์ -> CD -- ์ซ์ -- ์ฌ๋ฆผ SELECT CEIL(13.1) FROM DUAL; --๋ค์ ์ซ์๊ฐ ์์ผ๋ฉด ๋ฌด์กฐ๊ฑด ์ฌ๋ฆผ -> 14 -- ๋ด๋ฆผ SELECT FLOOR(13.9) FROM DUAL; -- ๋ด๋ฆผ์ฐจ์ -> 13 -- ๋๋ ๋๋จธ์ง SELECT MOD(3,2) FROM DUAL; -- == % -- ์ ๊ณฑ (์๋ฐ == Math.pow SELECT POWER(3,2) FROM DUAL; -- ๋ฐ์ฌ๋ฆผ SELECT ROUND(13.6) FROM DUAL; -- ๋ถํธ +=1 0=0 -= -1 -- SIGN SELECT SIGN(13.4) FROM DUAL; SELECT SIGN(0) FROM DUAL; SELECT SIGN(-13.4) FROM DUAL; -- ๋ฒ๋ฆผ(์์์ ๊ฐ์ ๋ ๋ฆผ) SELECT TRUNC(123.456) FROM DUAL; SELECT TRUNC(123.456,2) FROM DUAL; -- ๋ช์๋ฆฌ๊น์ง ๋ ๋ฆฌ๋? SELECT TRUNC(123.456,-1) FROM DUAL; -- 1์ ์๋ฆฌ๋ฅผ ๋ ๋ฆผ -> 120 -- ๋ฌธ์๋ฅผ ๋ฃ์ผ๋ฉด ASCII์ฝ๋ ๊ฐ์ด ๋์ค๋ ํจ์ SELECT ASCII('A') FROM DUAL; --๋ณํํจ์ TO_CHAR ์ค์โ
--๋ ์งํ(DATE)๋ฅผ ๋ฌธ์์ด(VARCHAR2)๋ก ๋ฐ๊ฟ๋ ์ ์ผ ๋ง์ด ์ฌ์ฉํจ SELECT SYSDATE FROM DUAL; -- ๋ฌธ์์ด์ฒ๋ผ ๋ณด์ผ๋ฟ DATE ํ์
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_DATE -- VARCHAR2 -> DATE -- 20210208 SELECT TO_DATE('20210208') FROM DUAL; -- ๋ ์งํ์ผ๋ก ๋ฐ๋ SELECT TO_DATE('20210208', 'YYYYMMDD') FROM DUAL; --TO_NUMBER VARCHAR2 -> NUMBER (==์๋ฐ์ parseInT()) SELECT TO_NUMBER('123')+45 FROM DUAL; --LAST_DAY (๋ง๋ ๊ตฌํ๊ธฐ) SELECT LAST_DAY('21/02/08') FROM DUAL; SELECT LAST_DAY('21-02-01') FROM DUAL; SELECT LAST_DAY('210201', 'YYMMDD') FROM DUAL; -- ์ ์ | cs |
'๐ฆ ๋ฐ์ดํฐ ๋ฒ ์ด์ค (DB) ๐ฆ > ๐ พ ์ค๋ผํด DB & SQL' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
Power, Trunc, Trim, Round๋ฑ ๊ฐ์ข ํจ์ 1# (0) | 2021.02.10 |
---|---|
ORDER BY , GROUP BY ๋ฌธ (0) | 2021.02.10 |
SELECT ๋ฌธ - ์ค๊ฐ์ ๊ฒ ์์ (0) | 2021.02.08 |
SELECT๋ฌธ - 2 (0) | 2021.02.08 |
SELECT ๋ฌธ - 1 (0) | 2021.02.08 |
๋๊ธ