Character Functions()

Sample Database :

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.

SyntaxINITCAP ( <Column Name> or ‘String’ )

Example :

Ques : Convert Ename of EMP Table into Proper Case

initcap

 

 

 

 

 

 

 

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.

SyntaxRTRIM ( <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.

SyntaxTRIM ( <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

System Defined Functions in SQL
Where Clause
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *