Oracle PL/SQL-Tipps

REM Text in Datum: 

SELECT to_date('01.01.2005') FROM dual; 

SELECT to_date('01.01.2005','DD.MM.YYYY') FROM dual; 

SELECT to_date('01.Jan.2005','DD.MON.YYYY','nls_date_language=german') FROM dual;  

SELECT TO_TIMESTAMP ('19-Sep-15 17:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL; =>19.09.15 17:10:10,123000000 

Datum in Text 

SELECT TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS') FROM dual; 

=>07.11.2006 10:05:35  

 SELECT TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF') FROM dual; 

=>10:09:23.343000 

SELECT TO_CHAR(SYSTIMESTAMP,'FF4') FROM dual; 

=>3280 

SELECT TO_CHAR(100.00,'L9G999D99', ' NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''€'' ') "Gehalt" FROM DUAL; 

=>€100,00 

Text in Zahl 

SELECT TO_NUMBER('8.778') FROM dual; 

SELECT TO_NUMBER('-Euro100','L9G999D99', ' NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''Euro'' ') "Gehalt" FROM DUAL; 

=>-100   

SELECT TO_NUMBER('-100,123','999D999', ' NLS_NUMERIC_CHARACTERS = '',.'' ') "Gehalt" FROM DUAL; 

=>-100,123      
Ersetzte NULL in etwas anderes: 

SELECT NVL(col,0) FROM mytab; 

-- Wenn Spalte col eine Zahl 

SELECT NVL(col,'NA') FROM mytab;

-- Wenn Spalte col ein Text #

SELECT NVL(NULL,SYSDATE) FROM dual; 

-- Wenn Spalte col ein Datum ist:

Text In Clob (ab 9i): 

SELECT TO_CLOB('Text') FROM dual; 

SELECT TO_LOB(long_column) FROM dual; 

Buchstabe IN ASCII: 

SELECT ASCII('A') FROM dual; 

=>65 
ASCII IN Buchstabe: 

SELECT CHR(65) FROM dual; 

=>A 
RAW IN HEX: 

SELECT rawtohex('AA') from dual;

 =>4141 
Syntax: TO_BINARY_DOUBLE(expr [,fmt [, 'nlsparam' ] ])
Syntax: TO_BINARY_FLOAT(expr [,fmt [, 'nlsparam' ] ]) 

SELECT 1234.56, TO_BINARY_FLOAT(1234.56) FROM dual;