Character Functions in oracle
To study these functions we take “product” table as example.
Selecting product table
They are divided as below:
1. LENGTH:- This function returns length of any string value or column with varchar datatype passed as argument.
Syntax
LENGTH(str)
In above syntax “str” is any string value or column with varchar datatype.
Use of LENGTH function
2. LOWER:- This function converts any string value or column with varchar datatype passed as argument into lower case.
Syntax
LOWER(str)
In above syntax “str” is any string value or column with varchar datatype.
Use of LOWER function
3. UPPER:- This function converts any string value or column with varchar datatype passed as argument into upper case.
Syntax
UPPER(str)
In above syntax “str” is any string value or column with varchar datatype.
Use of UPPER function
4. INITCAP:- This function returns any string value or column with varchar datatype passed as argument with the first letter of each word in upper case.
Syntax
INITCAP(str)
In above syntax “str” is any string value or column with varchar datatype.
Use of INITCAP function
5. SUBSTR:- This function returns part of any string value or column with varchar datatype passed as argument.
Syntax
SUBSTR(str,pos,length)
In above syntax “str” is any string value or column with varchar datatype, “pos” is starting position for extracting string from “str” and “length” is number of character to extract from “str”.
Use of SUBSTR function
6. LPAD:- This function returns string value or column with varchar datatype passed in argument with other string padded on left side passed as third argument.
Syntax
LPAD(str 1,n,str 2)
In above syntax “str 1” is any string value or column with varchar datatype, “n” total length of string after padding and “str 2” is string used for padding “str 1”.
Use of LPAD function
7. RPAD:- This function returns string value or column with varchar datatype passed in argument with other string padded on right side passed as third argument.
Syntax
RPAD(str 1,n,str 2)
In above syntax “str 1” is any string value or column with varchar datatype, “n” total length of string after padding and “str 2” is string used for padding “str 1”.
Use of RPAD function
8. LTRIM:- This function removes a set of string value from left side of string value or column with varchar datatype passed as argument.
Syntax
LTRIM(str,set)
In above syntax “str” is any string value or column with varchar datatype, “set” is string to be removed from “str”.
Use of LTRIM function
9. RTRIM:- This function removes a set of string value from right side of string value or column with varchar datatype passed as argument.
Syntax
RTRIM(str,set)
In above syntax “str” is any string value or column with varchar datatype, “set” is string to be removed from “str”.
Use of RTRIM function
10. TRANSLATE:- This function translates set of string with another set of string from string value or column with varchar datatype passed as argument.
Syntax
TRANSLATE(str,from set,to set)
In above syntax “str” is any string value or column with varchar datatype, “from set” is string to be translate or remove with “to set” from “str”.
Use of TRANSLATE function
11. REPLACE:- This function is similar to translate function but it works on string rather than set of character.
Syntax
REPLACE(str,from set,to set)
In above syntax “str” is any string value or column with varchar datatype, “from set” is string to be replace or remove with “to set” from “str”.
Use of REPLACE function
12. ASCII:- This function returns ascii value of any character and if we pass any string value or column with varchar datatype passed as argument then we get ascii value of first character of string.
Syntax
ASCII(char)
In above syntax “char” is any character.
Use of ASCII function
I hope this article will help you