SQL

Character Functions in Oracle

These functions are going to be working on string expressions and integer values. Oracle supports the following character functions, let us learn one after the other.

1.Length(): This function returns the length of the given string expression.

WAQ to display employee name along with length from the employee table?

WAQ to display employee number, name and salary from employee table whose name length is greater than 5?

WAQ to update employee salary as 80000 whose employee name length is equal to 4?

WAQ to delete employee details whose length is greater than or is equal to 5

2.ASCII(): This function returns the ASCII number for the given character.

3. CHR(): This function returns character of the given ASCII number.

4. Lower(): This function converts upper case characters into lower case characters.

WAQ to display employee names in lowercase characters from the employee table.

5.UPPER(): This function converts lower case characters into upper case characters.

WAQ to display employee names in upper case characters from the employee table.

6. LTRIM(): this function trims or removes the left side space of the given string expression. This function we can use in two ways those are, first one is single argument and second one is two arguments.

Single Argument of LTRIM() Example
Two Arguments of LRIM(), First one is string expression & second one is trimming characters

7. RTRIM(): This function trims or removes right side space of given string expression. This function we can use in two ways those are, first one is single argument and second one is two arguments.

RTRIM() with one argument
RTRIM() with two arguments

8. TRIM(): This function would remove the space from left side as well as right side of the given string expression.

9. INITCAP(): This function converts initial character as capital in the given string expression.

10. REPLACE(): This function is used to replace an existing characters with the specified characters in the given string expression. It contains three arguments, first one is the string expression, second one is the existing characters and third one is the replacing characters.

11. CONCAT(): This function will add two given expression and contains only two arguments that are string expression1 and string expression2.

12. SUBSTRING(): This function returns the required substring from the given string expression. It contains three arguments, first one is string expression, second one is starting position of the character and length of the characters.

13. LPAD(): This function will use when the given string is less than the specified length then the remaining spaces would full fill with the specified character on the left side of the string expression. It contains three arguments, first one is the string expression, second one is the length of the string and third one is the replacing character.

14. RPAD(): This function will use when the given string is less than the specified length then the remaining spaces would full fill with the specified character on the right side of the string expression. It contains three arguments, first one is the string expression, second one is the length of the string and third one is the replacing character.

About the author

karunakar

I completed the M.TECH, am enthusiastic to finding the gadgets & learning new things.

Add Comment

Click here to post a comment