Character Functions
Character functions can return both character and numeric values. The subsequent functions are character functions which return numeric values.
ASCII
Syntax
ASCII(char)
It will return the decimal representation in the database character set of the first byte of char. the function returns an ASCII value if the database character set is 7-bit ASCII.
Example
SELECT ASCII('Q') FROM DUAL
ASCII('Q')
----------
81
INSTR
Syntax
INSTR(char1,char2[,n[,m]])
Searches char1 starting with its nth character for the mth occurrence of char2 and returns the position of the character in char1 which is the first character of this occurrence. If n are negative then ORACLE counts and searches backward from the end of char1. The m value must be positive. The values of both n and m are default 1, meaning ORACLE starts searching at the first character of char1 for the first occurrence of char2. The return value is associatively to the starting of char1, regardless of the value of n, and is expressed in characters. The return value is should be 0 if the search is unsuccessful (if char2 does not appear m times after the nth character of char1).
Examples
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL
Instring
---------
14
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring" FROM DUAL
Reversed Instring
-----------------
2
INSTRB
Syntax
INSTRB(char1,char2[,n[,m]])
This is the similar as INSTR, except that n and the return value are expressed in bytes rather than in characters. INSTRB is equivalent to INSTR for a single-byte database character set.
Example
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes" FROM DUAL
Instring in bytes
-----------------
27
LENGTH
Syntax
LENGTH(char)
It will return the length of characters in char. The length includes all trailing blanks if char has datatype CHAR. If char is null, this function returns null.
Example
SELECT LENGTH('RADIANT') "Length in characters" FROM DUAL
Length in characters
--------------------
7
LENGTHB
Syntax
LENGTHB(char)
It will return the length of char in bytes. This function returns null if char is null. LENGTHB is equivalent to LENGTH for a single-byte database character set.
Example
Suppose a character set of double-byte database
SELECT LENGTHB('CANDIDE') "Length in bytes" FROM DUAL
Length in bytes
---------------
14