Common oracle functions (1)

Source: Internet
Author: User
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?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.