Common Oracle Functions

One, comma splicing field

SELECT Listagg (AA, ', ') within GROUP (ORDER by AA) as AA from * * * where id<5

Output results for example: 1,2,3,4

Second, time conditions

SELECT * from AAA where time >=to_date (' 2016-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

Iii. Query Time Type field, conversion format

Select To_char (TIMR, ' Yyyy-mm-dd hh24:mi:ss ') from AAA

Iv. InStr () and substr ()

The InStr () function determines a string to determine whether it contains the specified character.
Finds the specified character in a string, returning the position of the specified character that was found.
Grammar:
InStr (Sourcestring,deststring,start,appearposition)
InStr (' Source string ', ' target string ', ' Start position ', ' first occurrence ')

Sql> Select InStr (' abcdefgh ', ' de ') position from dual;
Output: 4 starting from 1, the D row is the four, so return 4.

Sql>select InStr (' Abcdefghbc ', ' BC ', 3) position from dual;
Output: 9 starting from the 3rd character, the 3rd character is C, so find the BC from the 3 string, return 9

Sql> Select InStr (' Qinyinglianqin ', ' Qin ', 1, 2) position from dual;
Output: 12 Starting with the 1th character, find the location of the 2nd occurrence of a substring

The SUBSTR () function returns a substring from the given character expression or Memo field.
Grammar:

SUBSTR (Cexpression,nstartposition [, ncharactersreturned])

cexpression Specifies the character expression or Memo field from which to return the string;

Nstartposition is used to specify the position of the returned string in a character expression or Memo field.
The ncharactersreturned is used to specify the number of characters returned, by default returning all characters before the value of the character expression ends.

Cases:

SUBSTR (' abcdefghijlkm ', 1,5) output "ABCDE"

SUBSTR (' abcdefghijlkm ', 6) output "FGHIJKLM"

SUBSTR (' abcdefghijlkm ',-2) output "km"

Common Oracle Functions