Character Manipulation Function – Substring in SQL

SUBSTR : 

  • Fetching the character.
  • Substring is denoted by SUBSTR  in SQL .
  • SUBSTR works with three arguments.

Query :  SELECT ( ‘ HELLO WORLD ‘ ,  1 , 5 )  FROM dual ;

  • Argument 1 – denotes required column or column name or character with any datatype such as varchar, date, number.
  • Argument 2 – indicates starting position with number data type.
  • Argument 3 – indicates number of character with number data type.
Left to Right 1 2 3 4 5 6 7 8 9 10
String H E L L O W O R L D
Right to Left(Reverse Order) -10 -9 -8 -7 -6 -5 -4 -3 -2 -1

List of arguments and its results are shown below table :

Arguments Results
SUBSTR( ‘ HELLOWORLD ‘, 1 , 5 ) HELLO
SUBSTR( ‘ HELLOWORLD ‘, 2 , 4 ) ELLO
SUBSTR( ‘ HELLOWORLD ‘, 3 , 2 ) LL
SUBSTR( ‘ HELLOWORLD ‘, 5 , 1 ) O
SUBSTR( ‘ HELLOWORLD ‘, 5 , 10) OWORLD
SUBSTR( ‘ HELLOWORLD ‘, 2 , 5) ELLOW
SUBSTR( ‘ HELLOWORLD ‘, 8 , 5 ) RLD
SUBSTR( ‘ HELLOWORLD ‘, 1 ) HELLOWORLD
SUBSTR( ‘ HELLOWORLD ‘, 3 ) LLOWORLD
SUBSTR( ‘ HELLOWORLD ‘,  1 , 0 ) NULL output
SUBSTR( ‘ HELLOWORLD ‘, 0 , 1 ) H
SUBSTR( ‘ HELLOWORLD ‘, 0 , 3 ) HELL
  • Starting position is 1 number of character is zero then output returns null value.
  • Starting position is 0 number of character is 1 then output  by default accept the starting position as 1.

Negative Values :

Arguments Results
SUBSTR( ‘ HELLOWORLD ‘ , -3 , 3) RLD
SUBSTR( ‘ HELLOWORLD ‘ , -5 , 5) WORLD
SUBSTR( ‘ HELLOWORLD ‘ , -6 , 8) OWORLD
SUBSTR( ‘ HELLOWORLD ‘ , 0, 0) null value
SUBSTR( ‘ HELLOWORLD ‘ , -1 , 1) null value
  • In negative values the number of character should be always positive value.