Character functions-(learning notes), character function learning notes

Character functions-(learning notes), character function learning notes

-- Character Functions
-- Upper () converts the character to uppercase
-- Lower () converts characters to lowercase letters
Select upper ('cloud'), LOWER ('cloud ')
FROM dual;
-- Query SMITH information, write smith to lowercase, and convert it to uppercase using UPPER
SELECT *
FROM emp e
WHERE e. ename = UPPER ('Smith ');
-- Query the names of all employees and capital the names of all employees.
SELECT e. empno, e. ename original name, INITCAP (e. ename) employee name first letter capitalized
FROM emp e;
-- Query the number and name of all employees and replace A with the character '_'
SELECT e. empno, e. ename Original Name,
REPLACE (ename, 'A', '_') with the letter _
FROM emp e;

SELECT e. empno, e. ename Original Name,
Replace "A" with the letter "_" in the Translate (ename, 'A _
FROM emp e;
-- Query all employee information whose name is 5
SELECT *
FROM emp e
Where length (e. ename) = 5;
-- Query the information of the first three employees whose names are JAM substr
SELECT *
FROM emp e
WHERE substr (e. ename, 0, 3) = 'jam ';
SELECT *
FROM emp e
WHERE e. ename LIKE 'jam % ';

-- Query the names of employees in all 10 departments, but the first three letters of the first employee are not displayed.
SELECT e. deptno, e. ename Original Name,
SUBSTR (e. ename, 4) does not display the names of the first three letters
FROM emp e
WHERE e. deptno = 10;
-- Display the names of all employees and the last three letters-negative algebra from the back to the front
Original Name of SELECT e. ename, 3 letters after SUBSTR (e. ename,-3)
FROM emp e;
-- LENGTH can also be used.
The original name of SELECT e. ename, the last three letters of SUBSTR (e. ename,-3,
SUBSTR (e. ename, LENGTH (e. ename)-2)
FROM emp e;
-- Substr subscript starts from 1. If it is set to 0, it automatically starts from 1 and is set to negative.
-- Returns the ASCII code of a specified character.
Select ascii ('A'), ASCII ('A') FROM dual;
-- Chr () converts ASCII to character
Select chr ('65'), CHR ('97 ') FROM dual;
Select ascii ('A'), ASCII ('A'), CHR ('65'), CHR ('97 ') FROM dual;

-- Remove spaces on the left
Select ltrim ('cloud') FROM dual;
-- Remove spaces on the right
Select ltrim ('cloud') Remove the left space,
RTRIM ('cloud') removes spaces on the right
FROM dual;

-- Remove 2 leading Spaces

Select ltrim ('cloud') Remove the left space,
RTRIM ('cloud') removes spaces on the right,
TRIM ('cloud') removes two sides of space
FROM dual;
-- LTRIM/RTRIM can also be intercepted.
Select ltrim ('xyzadams', 'xyz') Remove xyz from the left,
Remove ams from the right side of RTRIM ('xyzadams ', 'ams ')
FROM dual;
-- Character left and right Fill function LPAD (), RPAD ()
Select lpad ('cloud', 10 ,'*')Remove the character length and fill in the specified character on the left,
RPAD ('cloud', 10 ,'*')Remove the character length and fill in the specified character on the left,
RPAD (LPAD ('cloud', 10, '*'), 14 ,'*')Use both left and right fill
FROM dual;