Oracle Reference:String Functions. Date Functions.

=======================
TRUNSLATE
=======================
TRUNSLATE(input_string, from_string, to_string)

For example: 
TRUNSLATE('asdfg1234','a12','A90') => 'Asdfg9034'

- The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.
- You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database interprets the empty string as NULL, and if this function has a null argument, then it returns NULL.

Example: Check if string got only alphanumeric characters.
SELECT TRIM(TRANSLATE('Looking for weird characters @#$%!', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.0123456789', ' ')) FROM DUAL;

The return value would be only characters that could not be matched: '@#$%!'

=======================
INSTR
=======================

Return a position of character in String.
SELECT INSTR(input_string,look_for_string, start_position, return_occurrence)
For Example:
SELECT('Look for Space',' ') => 5
SELECT('Look for Space',' ',1) => 5
SELECT('Look for Space',' ',1,1) => 5
SELECT('Look for Space',' ',1,2) => 9

SELECT('Look for Space','X',1,2) => 0  (not found)

123456789
Look for Space

Get the SELECT part from INSERT INTO...SELECT SQL
SELECT SUBSTR('INSERT INTO bla bla bla SELECT kuku kuku kuku ',INSTR('INSERT INTO bla bla bla SELECT kuku kuku kuku ','SELECT')) FROM DUAL;
The result is: 'SELECT kuku kuku kuku'

Count number of occurrences of char inside a string
SELECT regexp_count('NNYNNNNNNNNN','Y') FROM DUAL;
SELECT LENGTH('NNYNNNNNNNNN') - LENGTH(REPLACE('NNYNNNNNNNNN','Y','')) FROM DUAL;

In both cases the result is 1.

===============================
String Functions
===============================
1. How to remove spaces, tabs, enter from a String.

SELECT REGEXP_REPLACE(TEST_CODE.TEXT,'[[:space:]]') 
FROM USER_SOURCE;

2. How to compare two packages
exp_invalid_pkg_date EXCEPTION;

SELECT COUNT(*)
  INTO delta
 FROM ALL_SOURCE@TEST_DB TEST_CODE, 
      ALL_SOURCE@PROD_DB PROD_CODE
WHERE -- trim(REPLACE(TEST_CODE.TEXT,' ','')) <> trim(REPLACE(PROD_CODE.TEXT,' ',''))
      REGEXP_REPLACE(TEST_CODE.TEXT,'[[:space:]]') <> REGEXP_REPLACE(PROD_CODE.TEXT,'[[:space:]]')
  AND TEST_CODE.NAME = 'MY_PACKAGE' AND TEST_CODE.TYPE = 'PACKAGE BODY' AND TEST_CODE.OWNER = 'USER_A'
  AND PROD_CODE.NAME = 'MY_PACKAGE' AND PROD_CODE.TYPE = 'PACKAGE BODY' AND PROD_CODE.OWNER = 'USER_A'       
  AND TEST_CODE.line=PROD_CODE.line
  AND REGEXP_REPLACE(TEST_CODE.TEXT,'[[:space:]]') IS NOT NULL;
            
IF delta > 0 THEN
   RAISE exp_invalid_pkg_date;
END IF;        

=======================
Date Functions
=======================

=======================
Find difference between two dates in seconds.
=======================
SELECT (DATE_A - DATE_B) * (24*60*60) AS time_in_seconds FROM DUAL;

=======================
Working with milliseconds
=======================

Milliseconds are not stored in DATE data type.

To use Milliseconds, need to work with TIMESTAMP data type.

SYSDATE - Return current system time in DATE format.

SYTIMESTAMP - Return current system time in  TIMESTAMP format.

CREATE TABLE DATES_TEST(
  name        VARCHAR2(100),
  date_a      DATE,
  timestamp_a TIMESTAMP
);


INSERT INTO DATES_TEST(name, date_a, timestamp_a) 
VALUES ('SYSDATE', SYSDATE, SYSDATE);

INSERT INTO DATES_TEST(name, date_a, timestamp_a) 
VALUES ('SYSTIMESTAMP', SYSTIMESTAMP, SYSTIMESTAMP);

INSERT INTO DATES_TEST(name, date_a, timestamp_a) 

VALUES ('TO_CHAR_TIMESTAMP', 

        TO_TIMESTAMP ('20170620 14:10:10.123000', 'YYYYMMDD HH24:MI:SS.FF'), 

        TO_TIMESTAMP ('20170620 14:10:10.123000', 'YYYYMMDD HH24:MI:SS.FF'));

commit;

SELECT name, 
       TO_CHAR(date_a, 'YYYYMMDD hh24:mi:ss') as date_field, 
       TO_CHAR(timestamp_a, 'YYYYMMDD hh24:mi:ss.FF') as timestamp_field
  FROM DATES_TEST;


NAME                 DATE_FIELD        TIMESTAMP_FIELD
-------------------- ----------------- ---------------------------
SYSDATE              20170621 05:00:30 20170621 05:00:30.000000
SYSTIMESTAMP         20170621 05:04:49 20170621 05:04:49.227173
TO_CHAR_TIMESTAMP    20170620 14:10:10 20170620 14:10:10.123000


Trying to fetch milliseconds from a DATE type field, would return error.
SELECT name, 
       TO_CHAR(date_a, 'YYYYMMDD hh24:mi:ss.FF'), 
       TO_CHAR(timestamp_a, 'YYYYMMDD hh24:mi:ss.FF') FROM DATES_TEST


ORA-01821: date format not recognized in oracle


Milliseconds Format
By default milliseconds are returned to 6 digits.
To change it, say to 2 digits, use FF2 format, insted of default FF. 


SQL> SELECT name, TO_CHAR(date_a, 'YYYYMMDD hh24:mi:ss'), TO_CHAR(timestamp_a, 'YYYYMMDD hh24:mi:ss.FF2') FROM ALEC_TEST;

SELECT name, 
       TO_CHAR(timestamp_a, 'YYYYMMDD hh24:mi:ss.FF') as default_format,
       TO_CHAR(timestamp_a, 'YYYYMMDD hh24:mi:ss.FF2') as two_digits 
FROM ALEC_TEST;

NAME                 DEFAULT_FORMAT              TWO_DIGITS
-------------------- --------------------------- ---------------------------
sysdate              20170621 05:00:30.000000    20170621 05:00:30.00
sysdate              20170621 05:04:49.227173    20170621 05:04:49.22
TO_CHAR_TIMESTAMP    20170620 14:10:10.123000    20170620 14:10:10.12

TIMESTAMP datatype
The TIMESTAMP datatype is an extension on the DATE datatype. 
In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. There are also two variants called TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. 
These timestamps also store time zone offset information.

Like dates, timestamps are stored using a binary date format. 

In the case of a TIMESTAMP this is 11 bytes long, while those with timezone information require 13 bytes.