Character functions-(learning Notes)

--Character functions
--upper () converts a character to uppercase
--lower () converts a character to lowercase
SELECT UPPER (' Liunan '), LOWER (' Liunan ')
from dual;
--Query Smith for information, write Smith in lowercase, convert to uppercase with upper
SELECT *
From EMP E
WHERE e.ename=upper (' Smith ');
--Query all employees ' names and capitalize all employee names and initials
SELECT e.empno,e.ename original name, Initcap (e.ename) Employee name First Capital letter
From EMP E;
--Query all employee's number, name, and replace all letter A in employee name with character ' _ '
SELECT E.empno,e.ename the original name,
Replace (ename, ' a ', ' _ ') the letter A with the character _
From EMP E;

SELECT E.empno,e.ename the original name,
Translate (ename, ' a ', ' _ ') the letter A is replaced by the character _
From EMP E;
--Check out all employee information with a name length of 5
SELECT *
From EMP E
WHERE LENGTH (e.ename) = 5;
--The first 3 of the query name is Jam employee information substr
SELECT *
From EMP E
WHERE substr (e.ename,0,3) = ' JAM ';
SELECT *
From EMP E
WHERE e.ename like ' jam% ';

--Check the names of all 10-door employees, but do not show the first 3 letters of the first employee
SELECT E.deptno,e.ename the original name,
SUBSTR (e.ename,4) does not display the first 3 letters of the name
From EMP E
WHERE e.deptno=10;
--show the names of all employees and the last 3 letters-negative algebra from backward forward
SELECT e.ename original name, SUBSTR (e.ename,-3) after 3 letters
From EMP E;
--can also be achieved with length
SELECT E.ename The original name, SUBSTR (e.ename,-3) after 3 letters,
SUBSTR (E.ename,length (E.ename)-2) after 3 letters
From EMP E;
--SUBSTR subscript is starting from 1, set to 0 will automatically start from 1, set to negative from the forward intercept
--Returns the ASCII code for the specified character
SELECT ASCII (' a '), ASCII (' a ') from dual;
--CHR () converts ASCII into characters
SELECT chr (' n '), Chr (' the ' ") from dual;
SELECT ASCII (' a '), ASCII (' a '), Chr (' n '), Chr (' "") from dual;

--Remove left space
SELECT LTRIM (' Liunan ') from dual;
--Remove the right space
SELECT LTRIM (' Liunan ') removes the left space,
RTRIM (' Liunan ') remove the right space
from dual;

--Remove 2 side blanks

SELECT LTRIM (' Liunan ') removes the left space,
RTRIM (' Liunan ') remove the right space,
Trim (' Liunan ') Remove 2-side space
from dual;
--ltrim/rtrim can also intercept
SELECT LTRIM (' xyzadams ', ' xyz ') to the left of XYZ,
RTRIM (' Xyzadams ', ' AMS ') to the right to remove AMS
from dual;
--character aligns, right fill function lpad (), Rpad ()
SELECT lpad (' Liunan ', ' Ten, ' * ') removes the character length and fills the specified character to the left,
Rpad (' Liunan ', ten, ' * ') removes the character length and fills the left padding with the specified character.
Rpad (Lpad (' Liunan ', ' Ten, ' * '), +, ' * ') with left and right padding
from dual;

Character functions-(learning Notes)