oracle常用函数案例
时间:2021-07-01 10:21:17
帮助过:35人阅读
INSTR函数
SELECT INSTR(
‘ HELLO WORLD‘,
‘H‘)
FROM DUAL;
--LTRIM RTRIM函数
SELECT LTRIM(
‘*HELLO=‘,
‘*‘)
FROM DUAL;
SELECT RTRIM(
‘=HELLO=‘,
‘=‘)
FROM DUAL;
SELECT RTRIM(
LTRIM(
‘*HELLO*‘,
‘*‘),
‘*‘)
FROM DUAL;
--TRIM函数
SELECT TRIM(
‘*‘FROM‘*HELLO*‘)
FROM DUAL;
--REPLACE函数
SELECT REPLACE(
‘ABCDE‘,
‘E‘,
‘123‘)
FROM DUAL;
--SUBSTR函数
SELECT SUBSTR(
‘ABCDE‘,
2,
2)
FROM DUAL;
--CEIL FLOOR函数
SELECT CEIL(
6.01)
FROM DUAL;
SELECT FLOOR(
6.999)
FROM DUAL;
--ROUND函数
SELECT ROUND(
3.456,
1)
FROM DUAL;
--TRUNC函数
SELECT TRUNC(
3.456,
1)
FROM DUAL;
--RXTRACT函数
SELECT
SYSDATE "DATE",
EXTRACT(YEAR FROM SYSDATE) "
YEAR",
EXTRACT(MONTH FROM SYSDATE) "
MONTH",
EXTRACT(DAY FROM SYSDATE) "
DAY",
EXTRACT(HOUR FROM SYSTIMESTAMP) "HOUR",
--HOUR匹配的结果没有加上时区,在中国运行的结果要小8个小时
EXTRACT(MINUTE
FROM SYSTIMESTAMP) "MINUTE",
EXTRACT(SECOND FROM SYSTIMESTAMP) "SECOND"
FROM DUAL;
--TO_CHAR函数
SELECT
TO_CHAR(SYSDATE,‘YYYY"年"MM"月"DD"日" HH24:MI:SS‘) "DATE"
FROM DUAL
oracle常用函数案例
标签:ceil world c函数 trunc replace span second extra sel