Hey alians,
SUBSTR is pretty cool when it comes to extract a substring out of a string in SQL. SUBSTR is a single row function that returns a char value.
Let’s look at the syntax first.
char
It can be a column name from a table or a string. If it is a char string, it must be enclosed in single quoration marks.
position
This parameter represents the position of the character in the string and it defines the first character of the substring. It means it should always be in integer. It can be a integer value or a column having integer values.
substring_length
It represents the length of substring in the output. It can be omitted. It can be a integer value or a column having integer values.
Key points:
- If position is 0, then it is treated as 1.
- If position is positive, then Oracle Database counts from the beginning of char to find the first character.
- If position is negative, then Oracle counts backward from the end of char.
- If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.
Example-1:
select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',0,5) SUBSTRING
from dual;
| STRING | SUBSTRING |
|---|---|
| admin@dbgeek.in | admin |
Example-2:
select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',2,4) SUBSTRING
from dual;
| STRING | SUBSTRING |
|---|---|
| admin@dbgeek.in | dmin |
Example-3:
select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',-9,9) SUBSTRING
from dual;
| STRING | SUBSTRING |
|---|---|
| admin@dbgeek.in | dbgeek.in |
Example-4:
select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',4) SUBSTRING
from dual;
| STRING | SUBSTRING |
|---|---|
| admin@dbgeek.in | in@dbgeek.in |
Example-5:
select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',4,0) SUBSTRING
from dual;
| STRING | SUBSTRING |
|---|---|
| admin@dbgeek.in | (null) |
Hope this blog helped you to understand SUBSTR function.
If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share it with your friends and colleagues on your social media. For more updates join my Facebook group and do like my Facebook page.
Thank you,
Kapil Kumar
