Shulou(Shulou.com)06/01 Report--

1, string interception

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

2. Find the location of the substring

Select instr ('abcfdgfdhd','fd') from dual

3, string concatenation

Select 'HELLO' | |' hello world' from dual

4, 1) remove the spaces in the string

Select ltrim ('abc') S1

Rtrim ('zhang') S2

Trim ('zhang') S3 from dual

2) remove prefixes and suffixes

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 (97) from dual

7, calculate the length of the string

Select length ('abcdef') from dual

8 lower (uppercase), 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 bit

11, decode [implements if.. then logic] Note: the first is an expression, and the last is a value that does not meet any of the conditions.

Select deptno,decode (deptno,10,'1',20,'2',30,'3',' other') from dept

Example:

Select seed,account_name,decode (seed,111,1000,200,2000,0) from t_userInfo// if seed is 111, take 1000,200,2000,0.

Select seed,account_name,decode (sign (seed-111), 1grammatical big seed',-1,'little seed','equal seed') from t_userInfo// shows large if seed > 111, small if it is 200, and equality for others.

12, case [implement switch.. case logic]

SELECT CASE X-FIELD

WHEN X-FIELD < 40 THEN 'X-FIELD < 40'

WHEN X-FIELD < 50 THEN 'X-FIELD < 50'

WHEN X-FIELD < 60 THEN 'X-FIELD < 60'

ELSE 'UNBEKNOWN'

END

FROM DUAL

Note: the CASE statement is very flexible in dealing with similar problems. Using Decode is more concise when you only need to match a small number of values

Original address: http://plat.delit.cn/thread-191-1-1.html

Please indicate the source of the reprint:

Written by: measurement Technology http://www.delit.cn

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.