Oracle PL/SQL Tutorial/Character String Functions/Introduction
Character Functions
Character Functions accept character input.
The input may come from a column in a table or from any expression.
Character Functions List
- ASCII(x) returns the ASCII value of the character x.
- CHR(x) returns the character with the ASCII value of x.
- CONCAT(x, y) concatenates y to x and return the appended string.
- INITCAP(x) converts the initial letter of each word in x to uppercase and returns that string.
- INSTR(x, find_string [, start] [, occurrence]) searches for find_string in x and returns the position at which find_string occurs.
- INSTRB(x) returns the location of a string within another string, but returns the value in bytes for a single-byte character system.
- LENGTH(x) returns the number of characters in x.
- LENGTHB(x) returns the length of a character string in bytes, except that the return value is in bytes for single-byte character sets.
- LOWER(x) converts the letters in x to lowercase and returns that string.
- LPAD(x, width [, pad_string]) pads x with spaces to left, to bring the total length of the string up to width characters.
- LTRIM(x [, trim_string]) trims characters from the left of x.
- NANVL(x, value) returns value if x matches the NaN special value (not a number), otherwise x is returned.
- NLS_INITCAP(x) Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.
- NLS_LOWER(x) Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.
- NLS_UPPER(x) Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.
- NLSSORT(x) Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.
- NVL(x, value) returns value if x is null; otherwise, x is returned.
- NVL2(x, value1, value2) returns value1 if x is not null; if x is null, value2 is returned.
- REPLACE(x, search_string, replace_string) searches x for search_string and replaces it with replace_string.
- RPAD(x, width [, pad_string]) pads x to the right.
- RTRIM(x [, trim_string]) trims x from the right.
- SOUNDEX(x) returns a string containing the phonetic representation of x.
- SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied.
- SUBSTRB(x) Same as SUBSTR except the parameters are expressed in bytes instead of characters to handle single-byte character systems.
- TRIM([trim_char FROM) x) trims characters from the left and right of x.
- UPPER(x) converts the letters in x to uppercase and returns that string.
11. 1. Introduction 11. 1. 1. Character Functions 11. 1. 2. <A href="/Tutorial/Oracle/0220__Character-String-Functions/CombiningFunctions.htm">Combining Functions</a>
Combining Functions
You can use any valid combination of functions in a SQL statement.
The following example combines the UPPER() and SUBSTR() functions;
The output from SUBSTR() is passed to UPPER().
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> SELECT first_name, UPPER(SUBSTR(first_name, 2, 2)) FROM employee;
FIRST_NAME UP
---------- --
Jason AS
Alison LI
James AM
Celia EL
Robert OB
Linda IN
David AV
James AM
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>