Common oracle functions (1)

1. returns the decimal number selectascii (A) A, ascii (z) a, ascii (12) A dozen, ascii () kgfromdual; 2. Return the selectchr (65) A and chr (122) zfromdual characters corresponding to the specified decimal. 3. connect two strings: selectconcat (bear size, bear 2) constrfromdual; -- bear

1. Return the decimal number corresponding to the specified character. select ascii (A) A, ascii (z) a, ascii (12) A dozen, ascii () kg from dual; 2. Return the select chr (65) A, chr (122) z from dual; 3. connect two strings: select concat (bear Big, bear two) constr from dual; -- bear

1. Return the decimal number corresponding to the specified character

Select ascii ('A') A, ascii ('Z') A, ascii ('12') a dozen, ascii ('') kg from dual;

2. returns the character corresponding to the specified decimal number.

select chr(65) A,chr(122) z from dual;

3. connect two strings

Select concat ('xiong da', 'xiong 2') constr from dual; -- Xiong daxiong 2

4. Change the first character to uppercase and return the string

select initcap('boat') upperfirst from dual;--Boat

5. Convert all characters into uppercase letters and return strings.

select upper('boat') upperall from dual t;--BOAT

6. Convert all characters into lowercase letters and return strings.

select lower('BoaT') lowerall from dual;--boat

7. INSTR (str1, str2, a, B) Functions

Usage: Obtain the position where str2 is contained in str1.

Check from the left. the start position is a. If a is a negative number, the scan starts from the right, and the position where B appears will be returned.

Both a and B are set to 1 by default, which will return the position where str2 appears for the first time in str1.

select instr('zheshigeceshi','sh',-2,1) str from dual;--11select instr('zheshigeceshi','sh',1,2) str from dual;--11

8. Get the string length

select length('boat') len from dual;--4

9. lpad (str, n, [pad_string]) Function

The str parameter can be a character or a parameter.

Parameter n: the length of the returned string. If the number is shorter than the length of the original string, the lpad function truncates the string into n characters from left to right;

The parameter pad_string is an optional parameter. This string is the string to be pasted to the left of the string. If this parameter is not written, the lpad function will paste a space on the left of the string.

select rpad('boat',10,'*') from dual t;--boat******select lpad('boat',10,'*') from dual t;--******boat

10. ltrim (x, y) Functions

Usage: truncates the characters in x one by one based on the characters in y, and starts from the left.

As long as some characters in y are met, all characters in x will be truncated until the Function Command ends until the characters in y are met. rtrim (y, x) is the same

select ltrim('boat','bo') from dual;--atselect ltrim('booooobbbbobat','bo') from dual t;--atselect rtrim('boat','at') from dual;--boselect rtrim('boaaaaaaaaatttttttaat','at') from dual;--bo

11. substr (string str, int a, int B) Function

Parameter 1: string to be processed by str

Parameter 2: a truncates the start position of the string (the start position is 0). If it is a negative value, it indicates that the string starts from the end.

Parameter 3: the length of the string intercepted by B. If a B-ultrasound returns the length of the string to be processed, the system returns the result based on the maximum length of the string to be processed.

If B is not used, all the remaining strings starting with a are obtained.

select substr('boatisgood',3,100) subs from dual;--atisgoodselect substr('boatisgood',3) subs from dual;--atisgoodselect substr('boatisgood',-3) subs from dual;--ood

12. Replace Functions

Select replace ('nba hupu pedestrian street has gone ', 'Pedestrian Street', 'bxj') from dual; -- why didn't nba hupu BXJ have gone?