Account
Sign in to you account
Sign In Sign Up

How to Split A String In Oracle Using Substr/Instr?

6 minutes read

To split a string in Oracle using SUBSTR and INSTR, you can use these functions in conjunction to extract substrings based on a delimiter or a specific pattern. The INSTR function is used to find the position of a substring within the given string, and the SUBSTR function is then used to extract the desired substring based on the position obtained from INSTR.


By iterating through the string and using these functions to extract substrings at each occurrence of the delimiter, you can effectively split the string into its individual components. This approach can be useful for breaking down a string into separate elements for further manipulation or analysis within your Oracle database.


How to split a string into substrings using INSTR in Oracle?

To split a string into substrings using INSTR in Oracle, you can use a combination of the INSTR function along with the SUBSTRING function. Here is an example of how you can achieve this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- Sample string
DECLARE
  str VARCHAR2(50) := 'Hello,World,How,Are,You';
  pos NUMBER;
BEGIN
  pos := INSTR(str, ',');
  
  WHILE pos > 0 LOOP
    DBMS_OUTPUT.PUT_LINE(SUBSTR(str, 1, pos - 1));
    str := SUBSTR(str, pos + 1);
    pos := INSTR(str, ',');
  END LOOP;
  
  -- Output the remaining substring
  IF LENGTH(str) > 0 THEN
    DBMS_OUTPUT.PUT_LINE(str);
  END IF;
END;


In this example, we start by declaring a sample string 'Hello,World,How,Are,You'. We then use the INSTR function to find the position of the comma in the string. We loop through the string, printing out the substring before the comma using the SUBSTR function, and then update the string to remove the substring that was just printed. We continue this process until there are no more commas in the string. Finally, we output the remaining substring if there is any left.


How to split a string and get the position of each part using INSTR in Oracle?

To split a string and get the position of each part using INSTR in Oracle, you can use the following steps:

  1. Use the INSTR function to find the position of the delimiter in the string. For example, if you want to split a string by a comma (','), you can use the following SQL query:
1
2
3
4

SELECT INSTR('apple,orange,banana', ',', 1, 1) AS pos1,
       INSTR('apple,orange,banana', ',', 1, 2) AS pos2,
       INSTR('apple,orange,banana', ',', 1, 3) AS pos3
FROM dual;


  1. The query above will return the position of each comma in the string 'apple,orange,banana'. The third parameter in the INSTR function specifies the starting position in the string to search for the delimiter. The fourth parameter specifies the occurrence of the delimiter to find.
  2. You can use these positions to extract each part of the string using the SUBSTR function. For example, to extract the first part ('apple') from the string, you can use the following query: 
1
2
3
4

SELECT SUBSTR('apple,orange,banana', 1, INSTR('apple,orange,banana', ',', 1, 1) - 1) AS part1,
       SUBSTR('apple,orange,banana', INSTR('apple,orange,banana', ',', 1, 1) + 1, INSTR('apple,orange,banana', ',', 1, 2) - INSTR('apple,orange,banana', ',', 1, 1) - 1) AS part2,
       SUBSTR('apple,orange,banana', INSTR('apple,orange,banana', ',', 1, 2) + 1, LENGTH('apple,orange,banana')) AS part3
FROM dual;


  1. The query above will extract each part of the string based on the positions obtained from the INSTR function. You can adjust the parameters of the SUBSTR function to extract the desired parts of the string.


By following these steps, you can split a string and get the position of each part using INSTR in Oracle.


How do you split a string into substrings using INSTR in Oracle?

To split a string into substrings using INSTR in Oracle, you can create a PL/SQL loop that iterates through the string and uses the INSTR function to find the position of a delimiter (such as a comma or space). You can then use SUBSTR function to extract the substrings based on the positions found by INSTR. Below is an example code snippet that demonstrates this approach:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

DECLARE
  v_string VARCHAR2(100) := 'apple,banana,cherry,grape';
  v_delimiter CHAR(1) := ',';
  v_start_pos NUMBER := 1;
  v_end_pos NUMBER;
  v_substring VARCHAR2(100);
BEGIN
  LOOP
    v_end_pos := INSTR(v_string, v_delimiter, v_start_pos);
    
    IF v_end_pos = 0 THEN
      v_substring := SUBSTR(v_string, v_start_pos);
    ELSE
      v_substring := SUBSTR(v_string, v_start_pos, v_end_pos - v_start_pos);
    END IF;
    
    DBMS_OUTPUT.PUT_LINE(v_substring);
    
    EXIT WHEN v_end_pos = 0;
    
    v_start_pos := v_end_pos + 1;
  END LOOP;
END;
/


In this example, the string 'apple,banana,cherry,grape' is split into substrings separated by commas. Each substring is then printed out. You can replace the delimiter and the input string in the code based on your specific requirements.


How to split a string and get the length of each part using SUBSTR in Oracle?

You can use the SUBSTR function in Oracle to split a string and get the length of each part. Here is an example query to achieve this:

1
2
3
4
5

SELECT SUBSTR('Hello World', 1, 5) AS part1,
       LENGTH(SUBSTR('Hello World', 1, 5)) AS part1_length,
       SUBSTR('Hello World', 7) AS part2,
       LENGTH(SUBSTR('Hello World', 7)) AS part2_length
FROM DUAL;


In this query:

  • We use the SUBSTR function to split the string 'Hello World' into two parts: the first 5 characters and the rest of the string starting from the 7th character.
  • We then use the LENGTH function to get the length of each part.


This will result in the following output:

1
2
3

PART1   | PART1_LENGTH | PART2     | PART2_LENGTH
--------|--------------|------------|-------------
Hello   | 5            | World     | 5



What is the difference between SUBSTR and INSTR when splitting a string in Oracle?

SUBSTR and INSTR are two different functions in Oracle that can be used to split a string, but they serve different purposes.


SUBSTR function is used to extract a substring from a larger string based on the starting position and length of the substring. For example, SUBSTR('Hello, World', 1, 5) would return 'Hello'.


INSTR function is used to find the position of a substring within a larger string. It returns the position of the first occurrence of a substring within a string. For example, INSTR('Hello, World', 'World') would return 8.


So, while SUBSTR is used to extract a substring from a larger string based on position and length, INSTR is used to find the position of a substring within a string.


How to split a string into multiple parts in Oracle using SUBSTR?

To split a string into multiple parts in Oracle using the SUBSTR function, you can specify the starting position and the length of each part you want to extract. Here is an example:

1
2
3
4
5

SELECT 
    SUBSTR('Hello World', 1, 5) AS part1,
    SUBSTR('Hello World', 7) AS part2
FROM 
    dual;


This query will split the string 'Hello World' into two parts: 'Hello' and 'World'. The first argument of the SUBSTR function is the string you want to split, the second argument is the starting position of the substring you want to extract, and the third argument is the length of the substring you want to extract. If you omit the third argument, the SUBSTR function will extract the substring from the starting position to the end of the string.

Facebook Twitter LinkedIn Telegram

Related Posts:

How to Separate Multiple String In Oracle?
In Oracle, you can separate multiple strings by using the SUBSTR and INSTR functions.The INSTR function is used to find the position of a substring within a string. You can use this function to identify the indexes where you want to separate the strings.Then, ...
How to Remove Nul Characters From String In Rust?
In Rust, you can remove null characters from a string by using the trim_matches function. You can specify the null character as the character to trim from the beginning and end of the string. For example, you can use the following code snippet to remove null c...
How to Convert A String to an Integer In Python?
To convert a string to an integer in Python, you can use the built-in int() function. Simply pass the string value as an argument to the int() function, and it will return the corresponding integer representation of the string. Keep in mind that the string mus...
How to Convert String to Json In Kotlin?
To convert a string to JSON in Kotlin, you can use the JSONObject class from the org.json package. First, create a new JSONObject instance by passing the string as a parameter to the constructor. This will parse the string and create a JSON object. You can the...
How to Validate Class String Property Is Not Empty In Kotlin?
In Kotlin, you can validate that a class string property is not empty by using the built-in function isNullOrEmpty() to check if the string is either null or empty. Here's an example code snippet to demonstrate how to validate a class string property is no...