LENGTH SUBSTR INSTR Functions in SQL

For Complete YouTube Video: Click Here

In this class, we will understand LENGTH SUBSTR INSTR Functions in SQL.

We have already discussed the concept of LTRIM and RTRIM.

To understand this, we will consider the table given below.

LENGTH: To find the size of a string.

To find the number of characters.

The syntax for the length function is as shown below.

LENGTH(string);

Below is the query to find the length of the names of the students.

SELECT LENGTH(name) FROM students;

The output of the query is as shown below.

SUBSTR: To clip out a piece of string.

The syntax for the substring function is as shown below.

SUBSTR(String, Start, [Count]);

The SUBSTR is the function name for a substring.

The first parameter is the string or the column name.

The second parameter is the starting position from where we want to clip off the string.

The third parameter is the count of the characters that we want to clip off from the string.

The parameters in the square brackets are optional.

If we don’t provide the count value, all the characters will be clipped off until the end.

The query with the SUBSTR and the output is as shown below.

INSTR: Allows a simple search for a set of characters through a string.

The syntax for INSTR is as shown below.

INSTR(String, set, [Start, [Occurrence]);

If we want to search for a set of characters in the string, we can use the INSTR.

The string parameter is the string or the values of the column.

The set parameter is the set of characters that we are searching for.

Start and occurrences are the optional parameters.

The start parameter is used for the start of the character from where we want the search to happen.

The occurrence parameter is the number of that string occurrence.

The query with the SUBSTR and the output is as shown below.