The character function is a function that accepts the character input value as the parameter and returns the character or numeric values. CHAR or VARCHAR character datatype converts into the numeric datatype like INTEGER or character datatype like CHAR or VARCHAR.
The commonly used character functions are as follows:
Let us study the above character functions one by one.
Syntax of the INSTR() function:
INSTR(string , substring [, starting_position [, occurrence]])
Example of the
SELECT INSTR('WELCOME TO ORACLE','O',1,3) FROM DUAL;
In this query, the position of the third 'o' is seen.
Example of the
SELECT INSTR('WELCOME TO ORACLE','O') FROM DUAL;
In this query, there is no position value and no occurrence value. So, the position of 'o' is searched for first 'o' in the given string.
SELECT INSTR('WELCOME TO ORACLE','O',-1,3) FROM DUAL;
In this query, the 'o' is counted from the backward. So, the position is 5.
PAD is nothing but filling the empty character with a character we wish. For example, consider we have a word called "Monday", has 6 letters and if we want to change it to have 10 letters word then we need to fill some dummy letters either Left side or right side. so that result would look like ****Monday or Monday####.
LPAD function is used to add a character in place empty on the Left Side. It is short for LEFT PAD. RPAD function is used to add a character in place empty on the Right Side. It is short for RIGHT PAD.
SELECT column_names, LPAD/RPAD(target_colum_nme, word_size, fill_character) FROM table_name;
SELECT first_name, LPAD(first_name, 10, '#'), RPAD(first_name, 10, '@') FROM Persons_table;
Check what happens when we use more than one fill character.
The TRIM function removes a specific character from the given values; if no character is specified then it will remove spaces.
The TRIM function removes the characters only from the ends, not something present in between the words.
SELECT TRIM (SIDE 'character' FROM column_name) column_name_for_output FROM table_name;
We can remove characters from either ends or both ends:
SELECT TRIM( ' xyz ' ) FROM dual;
SELECT TRIM (BOTH FROM first_name) name FROM Persons_table;
SELECT LTRIM('annahitha', 'a') FROM dual;
# removes the specific character from the LEFT side SELECT first_name, LTRIM( first_name, 'R' ) FROM persons_table;
# removes the spaces from the LEFT side SELECT TRIM (LEADING FROM first_name) name FROM Persons_table;
SELECT RTRIM('annahitha', 'a') FROM dual;
SELECT first_name, RTRIM( first_name, 'a' ) FROM persons_table;
# removes the spaces from the right side SELECT TRIM (TRAILING FROM first_name) name FROM Persons_table;