Oracle character functions

1, String intercept
Select substr (' abcdef ', 1,3) from dual

2, finding the substring location
Select InStr (' Abcfdgfdhd ', ' FD ') from dual

3, String connection
Select ' HELLO ' | | ' Hello world ' from dual;

4, 1) Remove spaces from the string
Select LTrim (' abc ') s1,
RTrim (' Zhang ') s2,
Trim (' Zhang ') S3 from dual

2) Remove the preamble and suffix
Select Trim (leading 9 from 9998767999) S1,
Trim (trailing 9 from 9998767999) S2,
Trim (9 from 9998767999) S3 from dual;

5, returns the ASCII value of the first letter of the string
Select ASCII (' a ') from dual

6, returns the letter corresponding to the ASCII value
Select CHR from dual

7, calculating the string length
Select Length (' abcdef ') from dual

8,initcap (capitalized), lower (lowercase), upper (uppercase)
Select lower (' ABC ') s1,
Upper (' def ') S2,
Initcap (' EFG ') S3
from dual;

9,replace
Select replace (' abc ', ' B ', ' XY ') from dual;

10,translate
Select Translate (' abc ', ' B ', ' xx ') from dual; --X is 1 bits

One by one, decode[implement if. Then logic] Note: The first is an expression, and the last is a value that does not meet any one of the criteria
Select Deptno,decode (deptno,10, ' 1 ', 20, ' 2 ', 30, ' 3 ', ' other ') from dept;
Cases:
Select Seed,account_name,decode (seed,111,1000,200,2000,0) from t_userinfo//if Seed is 111, take 1000; 200, fetch 2000; other FETCH 0
Select Seed,account_name,decode (seed-111), 1, ' Big Seed ', -1, ' Little seed ', ' equal Seed ') from t_userinfo//if seed >111, the display is large; 200, the display is small; others are displayed equal

case[implementation of switch. Case Logic]
SELECT Case X-field
When X-field < Max then ' X-field less than 40 '
When X-field < ' X-field less than 50 '
When X-field < ' X-field less than 60 '
ELSE ' Unbeknown '
END
From DUAL

Note: Case statements are very flexible when dealing with similar problems. Decode is more concise when only a small number of values need to be matched

The original address; http://plat.delit.cn/thread-191-1-1.html

Reprint please indicate the source;

Author: metric Technology www. Delit. cn

Oracle character functions