Character functions in SQL

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:

  • INSTR
  • LPAD
  • RPAD
  • TRIM

Let us study the above character functions one by one.

INSTR() function in the SQL:

The INSTR() function finds the substring in a string and also the position of the substring of the given string. This function is used to find the position of the character.

Syntax of the INSTR() function:

INSTR(string , substring [, starting_position [, occurrence]])
  • String : The string which contains the substring.
  • Substring : The substring is needed to be searched.
  • Starting position : The starting position is optional. if this is omitted, then the default value is always 1. The position where the string function needs to be searched. If the position is negative, then the function counts from the backward.
  • Occurrence : This is optional. The occurrence of the substring in the given string.

Example of the INSTR() function :

SELECT INSTR('WELCOME TO ORACLE','O',1,3) 
FROM DUAL; 

In this query, the position of the third 'o' is seen.
instr()-function

Example of the INSTR() function where the starting position and the occurrence are omitted.

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.
instr()-func

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.
instr-func-backward

LPAD and RPAD functions in SQL

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;

lpad-func

Check what happens when we use more than one fill character.

TRIM function in SQL

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:

  • LEADING: Removes the specified character from the left side
  • TRAILING: Removes the specified character from the right side
  • BOTH: Removes the specified character from both sides
  • NO Value: Same as BOTH
SELECT TRIM( '  xyz  ' )
FROM dual;

trim-dual

SELECT 
TRIM (BOTH FROM first_name) name 
FROM Persons_table;

trim-both-func

SELECT LTRIM('annahitha', 'a') FROM dual;

ltrim-dual

# removes the specific character from the LEFT side
SELECT first_name, 
LTRIM( first_name, 'R' ) 
FROM persons_table;

ltrim()

# removes the spaces from the LEFT side
SELECT 
TRIM (LEADING FROM first_name) name 
FROM Persons_table;

trim-leading

SELECT RTRIM('annahitha', 'a') FROM dual;

rtrim-dual

SELECT first_name, 
RTRIM( first_name, 'a' ) 
FROM persons_table;

rtrim()

# removes the spaces from the right side
SELECT 
TRIM (TRAILING FROM first_name) name 
FROM Persons_table;

trim-trailing

0 results
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions