Oracle_SUBSTR()

SUBSTR

admin Functions ,

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.

SUBSTR(char,position,{substring_length})

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:

  1. If position is 0, then it is treated as 1.
  2. If position is positive, then Oracle Database counts from the beginning of char to find the first character.
  3. If position is negative, then Oracle counts backward from the end of char.
  4. 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;
STRINGSUBSTRING
admin@dbgeek.inadmin

Example-2:

select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',2,4) SUBSTRING
from dual;
STRINGSUBSTRING
admin@dbgeek.indmin

Example-3:

select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',-9,9) SUBSTRING
from dual;
STRINGSUBSTRING
admin@dbgeek.indbgeek.in

Example-4:

select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',4) SUBSTRING
from dual;
STRINGSUBSTRING
admin@dbgeek.inin@dbgeek.in

Example-5:

select
'admin@dbgeek.in' STRING,
substr('admin@dbgeek.in',4,0) SUBSTRING
from dual;
STRINGSUBSTRING
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

Sharing is caring!

You May Also Like..

INSTR

Oracle_INSTR()

Hey alians, While working with the data, INSTR is a very important single row function that returns a numeric value. […]

NULLIF

Oracle SQL_Null Related functions-3_NULLIF

Hi Aliens, Today, we will talk about another Null Related Function, NULLIF. You can read my notes on NVL, NVL2 […]

Translate

Oracle – Translate Function

Hi Geeks, Today, we will talk about an Oracle’s inbuilt function TRANSLATE. TRANSLATE works same as REPLACE function does but […]

Leave a Reply

Your email address will not be published.