Oracle PL/SQL Einzel-Tipp ansehen

PL/SQL Konvertierungen

Tipp 2

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;