SQL query : Search sub string in column value string with delimeter

Input: String with proper delimeter as table column value
    ex:- /usr/tmp, /usr/tmp/psr, /usr/psr/dir1, /usr/psr/psr/dir1, /usr/lib
Output: substring between delimeter(,)
Note:
    1) This is SQL Query not PL/SQL program, its just a query
    2) User can search for only one string at a time
    3) User can change the search occurance i.e. need first occurance of the substring or nth occurance


SQL Query:


select substr(
        concat(',',concat(value1,',')),
        (instr(concat(',',concat(value1,',')), ',' , (instr(concat(',',concat(value1,',')),'psr',1,2) - length(concat(',',concat(value1,',')))),1)+1),
        (instr(value1, ',', (instr(concat(',',concat(value1,',')),'psr',1,2)),1)+1)-
        (instr(concat(',',concat(value1,',')), ',' , (instr(concat(',',concat(value1,',')),'psr',1,2) - length(concat(',',concat(value1,',')))),1)+1)
        )
as result
from check1


 Detailed Explanation:

1. Modify original string in this ",string," format
2. Search for the substring and find index of previous delimeter of the substring
3. Find the length of the substring

select substr(                        // value1 is column name
        // 1.Concatenating delimeter(,) before and after string
        concat(',',concat(value1,',')),

        // 2. Finding substring starting index
        (instr(concat(',',concat(value1,',')), ','
            , (instr(concat(',',concat(value1,',')),'psr',1,2)   
// Finding substring 'psr' 2nd occurance, user can change value 2 from (1 to n)th occurance
            - length(concat(',',concat(value1,','))))           
// Searching in reverse direction to find previous delimeter(,) index from substring
            ,1)                                    // Delimeter(,) index will be our starting of substring
            +1)                                    // Not including delimeter(,) in substring display

         // 3. Finding length of the substring
         // Finding next delimeter(,) index from substring
         ,(instr(value1, ',', (instr(concat(',',concat(value1,',')),'psr',1,2)) ,1)+1)-
      
        // Finding length between two delimeters(,) i.e. length of substring
        (instr(concat(',',concat(value1,',')), ',' , (instr(concat(',',concat(value1,',')),'psr',1,2) - length(concat(',',concat(value1,',')))),1)+1)
        )
as result                            // Displaying column as result
from check1                            // Table name check1