Oracle 字符函数

chr()

select chr(65 + level -1) from dual connect by level <= 10;

返回10行，分别是A-J

ascii()

select ascii('A') + level -1 from dual connect by level <= 10;

返回A-J的ascii值

to_number进制转换

10-->16

select to_number(10,'xxxx') from dual;

select to_number(10,'fmxxxx') from dual;

16-->10

select to_number('A','xx') from dual;

INSTR(string, substring, start_position, occurrence);

返回substring在string中的下标。1为起点。start_position 为正，从左至右查找，为负，从右到左查找。

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

假如指定的occurrence不存在，则返回0

$hr@ORCL> select instr('abc','a',1,10) from dual;

INSTR('ABC','A',1,10)
---------------------
                    0