Extract 함수
1. 개요 : 오라클에서 제공해주는 함수로 날짜(date), xml 타입의 데이터에서 특정값을 뽑아내는 함수
2. extract(datetime)
EXTRACT( { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
| TIMEZONE_HOUR
| TIMEZONE_MINUTE
| TIMEZONE_REGION
| TIMEZONE_ABBR
}
FROM { expr }
)
예제 :
EXTRACT(XMLType_instance, XPath_string [, namespace_string ])
예제 :

SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL; SELECT EXTRACT (MONTH FROM DATE '2012-04-13') FROM DUAL; SELECT EXTRACT (DAY FROM DATE '2012-04-13') FROM DUAL;3. extract(xml)

SELECT warehouse_name, EXTRACT(warehouse_spec, '/Warehouse/Docks') "Number of Docks" FROM warehouses WHERE warehouse_spec IS NOT NULL ORDER BY warehouse_name; WAREHOUSE_NAME Number of Docks ------------------------- ------------------------- New Jersey San Francisco <Docks>1</Docks> Seattle, Washington <Docks>3</Docks> Southlake, Texas <Docks>2</Docks> -- 참고 함수 : extractvalue SELECT warehouse_name, EXTRACTVALUE(e.warehouse_spec, '/Warehouse/Docks') "Docks" FROM warehouses e WHERE warehouse_spec IS NOT NULL ORDER BY warehouse_name; WAREHOUSE_NAME Docks -------------------- ------------ New Jersey San Francisco 1 Seattle, Washington 3 Southlake, Texas 2
최신 댓글