Upper():
- It returns specified string into the upper case.
Syntax:
Upper(String or Filed);
Example:
SELECT upper('Welcome') FROM DUAL; // with constant String
SELECT upper(firstName) FROM EMP ; // where firstName is a field
Output:
WELCOME // Output for static string example
Lower():
- It returns specified strings in to the lower case letters.
Syntax:
lower(String or Filed);
Example:
SELECT lower('WELCOME') FROM DUAL; // with constant String
SELECT lower(firstName) FROM EMP ; // where firstName is a field
Output:
welcome // Output for static string example
Length():
- It returns total length or number of characters of the given string.
Syntax:
length(String or Filed);
Example:
SELECT length('WELCOME') FROM DUAL; // with constant String
SELECT length(firstName) FROM EMP ; // where firstName is a field
Output:
7 // Output for static string example
SubStr():
- It is use to find sub string from a given string.
- It takes three arguments as follow.
Syntax:
substr(String, startPosition, noOfChar);
- Where string is the string from which substring is to be found
- Start position is the starting position from which Substring is to be found.
- No of character specified total number of character to be extract from the stating position.
Example:
SELECT substr('Welcome', 4, 4) FROM DUAL;
Output:
come // sub-string from 'Welcome' string
Instr():
- It is use search the specific character in to the given string.
Syntax:
instr(String, charString, noOfChar);
- Where string is the string in which searching will Perform.
- noOfChar specifies characters to be search into the given string.
- It returns index value on success.
Example:
SELECT instr (‘Welcome’, ’e’, 1) FROM DUAL;
Output:
2
Rtrim():
- It removes specified set of character from the given string on the right side.
Syntax:
rtrim(string, <charSet>);
Example:
SELECT rtrim('Welcome', 'met')FROM DUAL;
Output:
Welco
Ltrim():
- It removes specified set of character from the given string on the left side.
Syntax:
ltrim(string, <charSet>);
Example:
SELECT ltrim('Welcome', 'wet') FROM DUAL;
Output:
lcome
Translate():
- It translates are replace specified character set with the string to be replacing from a given string.
- It has one to one relationship for the replacement.
Syntax:
translate (<string1>, <string_to_replace>,<Replacement string>) ;
Example:
SELECT translate ( '1sct523', '123', '7a9' ) FROM DUAL ;
Output:
7sct5a9
Lpad ():
- It is use to apply padding on the left side to given value.
Syntax:
lpad(string, n , [paddingChar])
- String specifies target string to which padding is apply.
- Second argument specifies no of padding.
- Padding character specifies the character by which Padding will be applied.
Example:
SELECT lpad(‘page 1’, 10, ’*’) FROM DUAL ;
Output:
****page 1
Rpad ():
- Rpad applies padding from the right side to a given string.
Syntax:
rpad(string, n , [paddingChar])
- String specifies the target string to which padding is to be applied.
- Second arguments specify the no of padding.
- Padding characters specify the character by which padding will be apply.
Example:
SELECT rpad(‘page 1’, 10, ’*’) FROM DUAL ;
Output:
Page 1****
Initcap ():
- It is used to convert given string into initial capital letters.
- It will convert first character of each word into capital letter.
Syntax:
initcap (string);
Example:
SELECT initcap(‘welcome to oracle’) FROM DUAL ;
Output:
Welcome To Oracle