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

1. ASCII(x) returns the ASCII value of the character x.
2. CHR(x) returns the character with the ASCII value of x.
3. CONCAT(x, y) concatenates y to x and return the appended string.
4. INITCAP(x) converts the initial letter of each word in x to uppercase and returns that string.
5. INSTR(x, find_string [, start] [, occurrence]) searches for find_string in x and returns the position at which find_string occurs.
6. INSTRB(x) returns the location of a string within another string, but returns the value in bytes for a single-byte character system.
7. LENGTH(x) returns the number of characters in x.
8. LENGTHB(x) returns the length of a character string in bytes, except that the return value is in bytes for single-byte character sets.
9. LOWER(x) converts the letters in x to lowercase and returns that string.
10. LPAD(x, width [, pad_string]) pads x with spaces to left, to bring the total length of the string up to width characters.
11. LTRIM(x [, trim_string]) trims characters from the left of x.
12. NANVL(x, value) returns value if x matches the NaN special value (not a number), otherwise x is returned.
13. NLS_INITCAP(x) Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT.
14. NLS_LOWER(x) Same as the LOWER function except that it can use a different sort method as specified by NLSSORT.
15. NLS_UPPER(x) Same as the UPPER function except that it can use a different sort method as specified by NLSSORT.
16. NLSSORT(x) Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used.
17. NVL(x, value) returns value if x is null; otherwise, x is returned.
18. NVL2(x, value1, value2) returns value1 if x is not null; if x is null, value2 is returned.
19. REPLACE(x, search_string, replace_string) searches x for search_string and replaces it with replace_string.
20. RPAD(x, width [, pad_string]) pads x to the right.
21. RTRIM(x [, trim_string]) trims x from the right.
22. SOUNDEX(x) returns a string containing the phonetic representation of x.
23. 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.
24. SUBSTRB(x) Same as SUBSTR except the parameters are expressed in bytes instead of characters to handle single-byte character systems.
25. TRIM([trim_char FROM) x) trims characters from the left and right of x.
26. 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>