Character Functions
Characteristics
Single-row character functions accept character data as input and return either character or numeric values. Character functions that return character values return values of the same data type as their input argument. Character functions that return number values can take any character data type as their input argument.
Types
There are two types of single-row character functions:
- Case-manipulation functions
- Character-manipulation functions
Case-Manipulation Functions
You use case-manipulation functions to change the case of character strings. The types of case-manipulation functions are LOWER , UPPER , and INITCAP .
You use character-manipulation functions to manipulate character strings. The types of character-manipulation functions are CONCAT , SUBSTR , INSTR , LPAD , RPAD , TRIM , and REPLACE .
| Function | Description | Example |
|---|---|---|
| UPPER(<string>) | takes a string and converts any letters in it to uppercase, e.g., DNAME = upper(DNAME) (The name of a department must consist only of upper case letters.) | SELECT UPPER(’Large’) "Uppercase" FROM DUAL; |
| LOWER(<string>) | converts any letter to lowercase | SELECT LOWER(’SMALL’) "Lowercase" FROM DUAL; |
| INITCAP(<string>) | converts the initial letter of every word in |
SELECT INITCAP(’the soap’) "Capitals" FROM DUAL; |
| LENGTH(<string>) | returns the length of the string. | SELECT LENGTH(’CANDIDE’) "Length in characters" FROM DUAL; |
| concat(<string1>,<string2>) | CONCAT returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). | SELECT CONCAT(CONCAT(ename, ’ is a ’), job) "Job" FROM emp WHERE empno = 7900; |
| SUBSTR(<string>, n [, m]) | clips out a m character piece of <string>, starting at position n. If m is not specified, the end of the string is assumed. substr(’DATABASE SYSTEMS’, 10, 7) returns the string ’SYSTEMS’. | SELECT SUBSTR(’ABCDEFG’,3,4) "Substring" FROM DUAL; |
| instr(<string>, <substring>, <position> , <occurence>) | INSTR searches string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string) the return value is 0. |
SELECT INSTR(’CORPORATE FLOOR’,’OR’, 3, 2) "Instring" FROM DUAL; |
| LPAD(<char1>, n ,<char2>) | LPAD returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank | SELECT LPAD(’Page 1’,15,’*.’) "LPAD example" FROM DUAL; |
| RPAD(<char1>, n ,<char2>) | RPAD returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. | SELECT RPAD(’MORRISON’,12,’ab’) "RPAD example" FROM DUAL; |
| TRIM(<char> FROM <string>) | TRIM enables you to trim leading or trailing characters (or both) from a character string. | SELECT TRIM (’0’ FROM ’0009872348900’) "TRIM Example" FROM DUAL; |
| REPLACE(<char>, <search_string>, <replacemnt_string>) | REPLACE returns char with every occurrence of search_string replaced with replacement_string. | SELECT REPLACE(’JACK and JUE’,’J’,’BL’) "Changes" FROM DUAL; |
Write SQL query to show list of employee displaing the first name, followed by a space, followed by the last name, all in proper case and the e-mail data to appear in lowercase.