SQL- Character Functions || Case Conversion Functions|| SQL Series
Character Functions:
Character function accepts character data and return CHARACTER values.
Character functions return the data type VARCHAR2, limited to a length of 4000 Bytes.
When the Return value length exceeds, then the return value is truncated, without an error.
Types of Character Functions:
Case Conversion Functions:
These functions are used to convert the casing of the character data.
When the corresponding case of the data is same, it returns the data as supplied.
Character Manipulation Functions:
These functions will manipulate the given input data as per the applied standards of the function.
Character Manipulation Functions help us data manipulation in transactional statements.
Case Conversion Functions
These functions are used to convert the casing of the existing character from one type to another type.
LOWER Function
UPPER Function
INITCAP Function
LOWER Function:
Lower Function converts alpha character values to lowercase.
The return value has the same data type as argument CHAR type (CHAR or VARCHAR2).
Basic Syntax:
Lower (Column/Expression)
Examples:
SELECT LOWER(‘HP CORPORATION’) FROM DUAL;
SELECT LOWER(Ename) lowername, job, LOWER(job) lowerjob From EMP;
UPPER Function:
UPPER function converts alpha character values to upper case.
The return value has the same data type as the argument CHAR type (CHAR or VARCHAR2).
Basic Syntax:
UPPER(Column/Expression)
Examples:
SELECT ‘hp corporation’ orgstring, UPPER(‘hp corporation’) UPPER FROM DUAL;
SELECT Ename OrgEname, UPPER(Ename) UpperEname, Job orgJob, UPPER(JOB) UpperJob
From emp;
SELECT Ename||’’’s Designation is ‘||LOWER(Job) “Employees Designation”
FROM EMP
WHERE JOB IN(‘MANAGER’,’CLERK’,’Analyst’);
INITCAP Function
INITCAP Function converts the alpha character values into upper case for the first letter of each word, keeping all other letters in lower case.
By default, oracles SQL delimits the words by white space or special characters that are not alphanumeric by nature.
Basic Syntax:
INITCAP(Column/Expression)
Examples:
SELECT ‘oracle corporation’ orgstring, INITCAP(‘oracle corporation’) Initcap
From DUAL;
Note:
The Default Delimiter for word in oracle is
1.White Space
2.Any Special Character
Examples 2:
Write a Query to display job column values first letter into upper case