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.