Character Functions()
Sample Database :
There are following Character Functions().
1. INITCAP()
It converts the Initial letter of every word into Upper Case and rest in Lower case. This changes are only temporary and done only in the buffer.
Syntax : INITCAP ( <Column Name> or ‘String’ )
Example :
Ques : Convert Ename of EMP Table into Proper Case
Ques : Convert string ‘uttar pRADESH’ into proper case .
Soln : SELECT INITCAP(‘ uttar pRADESH’) FROM DUAL;
Output – Uttar Pradesh
2. LTRIM()
It trims the spaces if present from the left side of the string.
Syntax :LTRIM ( <Column Name> or ‘String’ )
Example :
Ques : Remove the spaces from Ename column of EMP table
Soln : SELECT LTRIM ( ENAME) FROM EMP;
Ques : Remove the space in ‘ Apple’ .
Soln : SELECT LTRIM(‘ Apple ‘) FROM DUAL;
Output – ‘Apple ‘
3. RTRIM()
It trims the spaces if present from the right side of the string.
Syntax : RTRIM ( <Column Name> or ‘String’ )
Example :
Ques : Remove the right spaces from Ename column of EMP table
Soln : SELECT RTRIM ( ENAME) FROM EMP;
Ques : Remove the space in ‘ Apple ‘ .
Soln : SELECT LTRIM(‘ Apple ‘) FROM DUAL;
Output – ‘ Apple’
4. TRIM()
It trims the spaces if present from the both sides of the string.
Syntax : TRIM ( <Column Name> or ‘String’ )
Example :
Ques : Remove the spaces from Ename column of EMP table
Soln : SELECT TRIM ( ENAME) FROM EMP;
Ques : Remove the space in ‘ Apple ‘
Soln : SELECT TRIM(‘ Apple ‘) FROM DUAL;
Output – ‘Apple’
5. UPPER()
It is used to convert all the characters of the string into upper case.
Syntax : UPPER (<Column name> or ‘String’)
Example :
Ques : Display the name of all the employees in upper case.
Soln : SELECT UPPER( ENAME ) FROM EMP;
Ques : Convert the string ‘strawberry’ into upper case.
Soln : SELECT UPPER(‘strawberry’) ROM DUAL;
Output : STRAWBERRY
6. LOWER()
It is used to convert all the characters of the string into lower case.
Syntax : LOWER (<Column name> or ‘String’)
Example :
Ques : Display the name of all the employees in upper case.
Soln : SELECT LOWER( ENAME ) FROM EMP;
Ques : Convert the string ‘strawberry’ into upper case.
Soln : SELECT UPPER(‘STRAWberry’) ROM DUAL;
Output : strawberry
7. LENGTH()
It is used to determine the length of the specified column or string.If the string contains spaces, then it is also counted as space has a ASCII value associated with it.
Note : If the column value is of datatype CHAR ( as CHAR is fixed length) then it returns the fixed length defined irrespective of the actual length of the value present. On the other hand if the column value is of datatype varchar2( varchar is of variable length) then it returns the actual length of the value present.
Syntax : LENGTH (<Column name> or ‘String’)
Example :
Ques : Display the length of the names of all the employees. ( assume datatype of ENAME column is CHAR(10)
Soln : SELECT LENGTH( ENAME ) FROM EMP;
Ques : Display the length of the names of all the employees. ( assume datatype of ENAME column is VARCHAR2(10)
Soln : SELECT UPPER(‘STRAWberry’) ROM DUAL;
Output : strawberry
