Basic Oracle Tutorial: single-line functions-character Functions

Character Type
ASCII
CHR
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTH
INSTR
TRIM
Dump
Lpad
Rpad
REPLACE
 
ASCII ('characters ')
Returns the ASCII value of a character.
Idle> select ASCII ('A') from dual;
ASCII ('A ')
----------
97

Idle> select ASCII ('A') from dual;

ASCII ('A ')
----------
65

Idle>

CHR ('n ')
Returns the character value n of n, which is the ASCII number.
Idle> select chr (65) from dual;

C
-
A

Idle> select chr (39) from dual;

C
-
'

Idle>

However, the ASCII code format for single quotes is very special. There are two single cited tables with one single cited

Idle> select ASCII (''') from dual;
ERROR:
ORA-01756: quoted string not properly terminated

Idle> select ASCII (''') from dual;

ASCII ('''')
-----------
39

Idle>

LOWER (column name | expression)
Lowercase Conversion
Idle> select lower ('abc') from dual;

LOW
---
Abc

Idle>
Dual is a virtual table. When a statement does not need to obtain data from the table but maintains the SQL syntax, Oracle provides a virtual table to solve this problem.

UPPER (column name | expression)
Capital Conversion

Idle> select upper ('abc') from dual;

UPP
---
ABC

Idle>

INITCAP (column name | expression)
Uppercase and lowercase letters for each word

Idle> select initcap ('abc dEf xyz') from dual;

INITCAP ('AB
-----------
Abc Def Xyz

Idle>

CONCAT (column name | expression, column name | expression)
Connect the first string to the second string

Idle> select concat ('abc', 'xyz') from dual;

CONCAT
------
Abcxyz

Not commonly used, because we can use | connection
Idle> select 'abc' | 'xyz' from dual;

'Abc' |
------
Abcxyz

Idle>

SUBSTR (column name | expression, m, [n])
Returns the child string starting from the m character. The length of the Child string is n. If n is not specified, the Child string starts from m to the end.

Idle> select substr ('abcdefxy',) from dual;

SUB
---
Def

Idle> select substr ('abcdefxy', 4) from dual;

SUBSTR
------
Defxyz

Idle>

LENGTH (column name | expression)
Returns the length of a string.
Idle> select length ('abcdefxyz ') from dual;

LENGTH ('abcdefxyz ')
-------------------
9

Idle>

INSTR (column name | expression, 'string', [m], [n])
Searches for the position of a given string from an expression or column. m indicates the position where the string is located. n indicates the number of occurrences. m and n are both 1 by default.

Idle> select INSTR ('abcddxyz', 'D') from dual;

INSTR ('abcddxyz', 'D ')
----------------------
4

Idle>
Idle> select INSTR ('abcddxyz', 'D', 5) from dual;

INSTR ('abcddxyz', 'D', 5)
------------------------
5

Idle> select INSTR ('abcddxyz', 'D', 5, 2) from dual;

INSTR ('abcddxyz', 'D', 5, 2)
--------------------------
6

Idle>

TRIM ([leading |] trailing | the character FROM source string to be removed FROM both)
You can use leading tailing to remove the specified characters from the source string. The removed string is at the beginning or end. By default, both strings are truncated with spaces.

Idle> select trim ('A' from 'aaabcdeaaafxyzaaa') from dual;

TRIM ('A' FRO
-----------
Bcdeaaafxyz

Idle> select trim (leading 'A' from 'aaabcdeaaafxyzaaa') from dual;

TRIM (LEADING 'a
--------------
Bcdeaaafxyzaaa

Idle> select trim (trailing 'A' from 'aaabcdeaaafxyzaaa') from dual;

TRIM (TRAILING'
--------------
Aaabcdeaaafxyz

Idle>

Left fill lpad right fill rpad
Fill the position of less than 20 characters with the specified symbol.
Idle> select lpad (ename, 20, '-'), rpad (ename, 20, '-') from emp where ename like's % ';

LPAD (ENAME, 20, '-') RPAD (ENAME, 20 ,'-')
----------------------------------------
--------------- SMITH ---------------
--------------- SCOTT ---------------
Idle>

1 2 Next Page