The usual functions for handling strings are as follows :
No. |
Name of function |
Meaning |
1 |
UPPER (C1) |
To capitalize all strings |
2 |
LOWER (C1) |
Convert all strings to lowercase |
3 |
Initcap (C1) |
Capitalize the first letter of a string |
4 |
LENGTHC (C1) |
Returns the length of a string |
5 |
SUBSTR (C1,n1[,n2]) |
Gets the substring of the string,N1 is the specified starting index,n2 is the specified number of numbers, and if n2is not written, the last one istaken. |
6 |
REPLACE (C1,C2) |
String substitution,C1 is the old sub-character,C2 is the new substring |
topic 1: Convert the string "WelcomeTozibo" to uppercase.
Select Upper (' Welcometozibo ') from dual; |
Topic 2: convert all employee names in the EMP table to lowercase.
Select Lower (ename) from EMP; |
Note: The Oracle database is case-sensitive.
The SqlPlus command receives the user input syntax :
Note : Digital Direct Write , string Single quotation mark .
General user input does not write single quotation marks , and the case is not considered.
Select ' &input ' from dual; Select Upper (' &input ') from dual; |
Topic 3: The employee's name is entered by the user, and all information about the employee is displayed.
SELECT * from EMP where ename= Upper (' &input ') |
Topic 4: Query the emp table for all employee names and return the initials capitalized.
Select Initcap (ename) from EMP; |
Topic 5: Query employee number, employee name, and position for all employees with employee name length 5.
Select Empno,ename,job From EMP where length (ename) = 5; |
Topic 6: Get the string ' Welcometozibo ' substring ' Zibo '.
Select substr (' Welcometozibo ', 10,4) from dual; |
Topic 7: Get the string ' Welcometozibo ' substring ' come '.
Select substr (' Welcometozibo ', bis) from dual; |
Note: Oracle 's index starts at 1 and is set to 0 in a timely manner, starting with 1 .
Title 8: Obtain the name of the employee of each employee of the EMP table, and the first two of the employee's name.
Select Ename, SUBSTR (ename,1,2) from EMP; |
Title 9: Obtain the name of the employee of each employee of the EMP table, and the last two digits of the employee's name.
Select Ename,substr (ename,-2) from EMP; |
title : Obtain the name of the employee of each employee of the EMP table, the first two digits of the employee's name, and the last two digits as the password.
Select ename, substr (ename,1,2) | | SUBSTR (ename,-2) as passwd from EMP |
Oracle Single-line function