Unlocking Oracle's String Functions: SUBSTR and INSTR

SUBSTR and INSTR SQL Oracle

Understanding Oracle’s String Functions

Oracle provides several string functions to manipulate and extract data from strings. Two of these functions, SUBSTR and INSTR, are commonly used in database queries. In this article, we will delve into the world of Oracle’s string functions and explore how they work.

Introduction to SUBSTR

The SUBSTR function is used to extract a specified number of characters from a string. It returns the substring starting at a specific position, with a specified length. The syntax for the SUBSTR function in Oracle is:

SUBSTR (expression, start_position [ , length ])

Where:

expression is the string from which to extract characters.
start_position is the position where you want to begin extracting characters. Positions are 1-based, meaning the first character of a string is at position 1.
length is the number of characters to extract.

Understanding INSTR

The INSTR function returns the position of a specified character within a string. It returns 0 if the specified character is not found in the string. The syntax for the INSTR function in Oracle is:

INSTR (expression, pattern [ , start_position ])

Where:

expression is the string in which to find characters.
pattern is the character or substring you want to find.
start_position is the position at which to begin searching for the specified character. Positions are 1-based.

The Example Query

The query provided in the Stack Overflow question:

SELECT PHONE, SUBSTR(PHONE, 1, INSTR(PHONE, '-') -1)
FROM DIRECTORY;

At first glance, this query seems confusing because it returns a value of 362, which doesn’t seem to make sense given the original string value of 362-127-4285.

Breaking Down the Query

To understand why the query returns the expected result, let’s break down how INSTR and SUBSTR work together:

Finding the Position of -:
- The INSTR(PHONE, '-') function finds the position of the first occurrence of '-' in the string.
- In this case, since '-' is at the second character (index 2) of the original string (362-127-4285), INSTR(PHONE, '-') returns a value of 2.
```
INSTR('362-127-4285', '-') = 2 
```
- The -1 in the query is used to get the position without the length of the substring. In Oracle’s INSTR function, if you omit the length, it returns the starting position.
```
INSTR('362-127-4285', '-') = 2 
INSTR(PHONE, '-') -1 = 2 - 1 = 1 
```
- The INSTR function with -1 returns the character at index 1. In this case, it’s '3'.
```
INSTR('362-127-4285', '-') = 2 
SUBSTR(PHONE, 1, INSTR(PHONE, '-') -1) = SUBSTR('362-127-4285', 1, 1)
                                            = '3'
                                            ```
```
Extracting the Substring:
- The SUBSTR function extracts a substring starting at position 1, with a length of 3.
- Since we want to extract characters up until but not including the next occurrence of '-', the INSTR value of 1 is used as both the start and end positions for the SUBSTR.
```
SUBSTR(PHONE, 1, INSTR(PHONE, '-') -1) = SUBSTR('362-127-4285', 1, 3)
                                        = '362'
                                        ```
```

In summary, the query returns 362 because:

The INSTR function finds the position of '-' in the string and subtracts 1 from it to get the starting position of the substring.
The SUBSTR function extracts a substring starting at position 1, with a length of 3.
Both functions work together to extract the substring without including any characters after the first occurrence of '-'.

Conclusion

The provided query may seem counterintuitive at first, but by breaking down how INSTR and SUBSTR work, we can see that it’s actually extracting a meaningful substring from the original string.

When working with strings in Oracle, understanding these functions is crucial for accurately manipulating and analyzing data. By using INSTR to find positions and SUBSTR to extract substrings, you can perform complex operations on character data.

Last modified on 2023-07-21