Single row character function are those function which accepts character values as arguments and return either a numeric or character values. These are two types:
- Case Manipulation
- Character Manipulation
These are explained as following below.
1. Case Manipulation:
It accept the character values and return the character value.
- UPPER(string1): Converts all letters of string1 into upper case.
- LOWER(string1): Converts all letters of string1 into lower case
- INITCAP(string1): Convert into upper case to first character of string1 of each word.
| Example | Result |
|---|---|
| SELECT UPPER(‘c plus plus’) FROM dual | C PLUS PLUS |
| SELECT LOWER(‘C PLUS PLUS’) FROM dual | c plus plus |
| SELECT INITCAP(‘c plus plus’) FROM dual | C Plus Plus |
2. Character Manipulation:
It accept the character values and return either charcter or numeric values.
- CONCAT(string1|expression1,string2|expression2): Concatenates(or join) strings(or expression), string1 and string2 together.
- SUBSTR(string1,start_positon,number_of_character): Extract substring from string1.
- LENGTH(string1): Numeric returns number of character in the given string1.
- INSTR(string1,string2): Return numeric values of first occurence of string1 in given string2.
- LPAD(string1,n,c): Pads the string1 to left upto the given lenght n with the given character c.
- RPAD(string1,n,c): Pads the string1 to right upto the given lenght n with the given character c.
- TRIM([leading|trailing|both] [char-set] from string1): Remove the specfied character either from beginning or end or both side of the string1.
- LTRIM(string1,[,char-set]): Trims all the specified character(or blank spaces) to the left side of the string1.
- RTRIM(string1,[,char-set]): Trims all the specified character(or blank spaces) to the right side of the string1.
| Example | Result |
|---|---|
| SELECT CONCAT(‘Hello ‘,’GEEKS !’) FROM dual | Hello GEEKS ! |
| SELECT SUBSTR(‘HelloGEEKS’,6,5) FROM dual | GEEKS |
| SELECT INSTR(‘HelloGEEKS’,’E’) FROM dual | 7 |
| SELECT LPAD(‘HelloGEEKS’,12,’@’) FROM dual | @@HelloGEEKS |
| SELECT RPAD(‘HelloGEEKS’,12,’@’) FROM dual | HelloGEEKS@@ |
| SELECT TRIM(‘H’ from ‘HelloGEEKS’) FROM dual | elloGEEKS |
| SELECT TRIM(‘l’ from ‘HelloGEEKS’) FROM dual | HelloGEEKS |
| SELECT TRIM(both ‘@’ from ‘@@HelloGEEKS@2@@’) FROM dual | HelloGEEKS@2 |
| SELECT LTRIM(‘@@HelloGEEKS@2@@’,’@’) FROM dual | HelloGEEKS@2@@ |
| SELECT RTRIM(‘@@HelloGEEKS@2@@’,’@’) FROM dual | @@HelloGEEKS@2 |
NOTE: Trim function is also used to remove spaces from beginning or end or both the side of string.