Oracle character Functions

Oracle character Functions
1. String truncation select substr ('abcdef', 1, 3) from dual 2, find the sub-string position select instr ('abcfdgfdhd', 'fd ') from dual 3, string connection select 'hello' | 'Hello world' from dual; 4, 1) Remove the space in the string select ltrim ('abc') s1, rtrim ('zhang ') s2, trim ('zhang ') s3 from dual 2) Remove the leading 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') fr Om dual 6, returns the ascii value corresponding to the letter select chr (97) from dual 7, calculates the string length select length ('abcdef ') from dual 8, initcap (uppercase first letter ), lower, upper 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, and decode [implements if .. then logic] Note: the first is the expression, and the last is the select deptno, decode (dept No, 10, '1', 20, '2', 30, '3', 'others') from dept; example: select seed, account_name, decode (seed, 111,1000, 111, 1000, 0) from t_userInfo // If seed is 200, take 2000; 111, take; other take 0 select seed, account_name, decode (sign (seed ), 1, 'Big seed',-1, 'Little seed', 'equal seed') from t_userInfo // If seed> 111, it is large; if it is 200, it is small; others are displayed as equal to 12. case [implements switch .. case Logic] select case X-FIELD WHEN X-FIELD <40 THEN 'x-FIELD less than 40' WHEN X-FIELD <50 THEN 'x-FIELD less than 50' WHEN X-FIELD <60 THEN 'x-FIELD less than 60' ELSE 'unbeknown 'end from dual Note: CASE statements are very flexible in dealing with similar issues. Decode is more concise to match a small number of values.