substr() Function in SQL

SUBSTR

• This will be used to extract substrings.
• To  show the specific character of a string.
• It will return character values

Syntax:

substr (string, start_chr_count [, no_of_chars])

Example

select substr('computer',2), substr('computer',2,5), substr('computer',3,7) from
dual;

output on tool

The query

        COUNT	  1    2    3    4   5   6   7   8
	STRING	  C    O    M    P   U   T   E   R
	COUNT	  -8  -7   -6   -5  -4  -3  -2  -1

substr(‘computer’,2) means –> Its count starting from 2 onwords TO last of string.

output should be [ OMPUTER]

substr(‘computer’,2,5) means –> Its count starting from 2 TO 6 of string.

output should be [ OMPUT]

substr(‘computer’,3,7) means –> Its count starting from 3 TO 9 last of string.

output should be [ OMPUTER]

Some More Example

select substr('computer',-4,4) from
dual;

substr(‘computer’,-4,4) means –> Its count starting from left side of string i.e -4 TO -1 (see on above picture table).

output should be [ UTER ]

select substr('computer',-1,2) from
dual;  

substr(‘computer’,-1,2) means –> Its count starting from left side of string i.e -1TO last of string (see on above picture table).

OUTPUT On Tool

Note:-

1. If no_of_chars parameter is negative then it will display nothing.
2. If both parameters except string are null or zeros then it will display nothing.
3. If no_of_chars parameter is greater than the length of the string then it ignores and calculates based on the orginal string length.
4. If start_chr_count is negative then it will extract the substring from right end.