How to cut varchar/text before n'th occurence of delimiter? PostgreSQL

Question

I have strings (saved in database as varchar) and I have to cut them just before n'th occurence of delimiter.

Example input:

String: 'My-Example-Awesome-String'
Delimiter: '-'
Occurence: 2

Output:

My-Example

I implemented this function for fast prototype:

CREATE OR REPLACE FUNCTION find_position_delimiter(fulltext varchar, delimiter varchar, occurence integer)
  RETURNS varchar AS
$BODY$

DECLARE
    result varchar = '';
    arr text[] = regexp_split_to_array( fulltext, delimiter);
    word text;
    counter integer := 0;

BEGIN
    FOREACH word IN ARRAY arr LOOP
        EXIT WHEN ( counter = occurence  );
        IF (counter > 0) THEN result := result || delimiter;
        END IF;
            result := result || word;
            counter := counter + 1;
    END LOOP;
    RETURN result;
END;


$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
SELECT find_position_delimiter('My-Example-Awesome-String', '-', 2);

For now it assumes that string is not empty (provided by query where I will call function) and delimiter string contains at least one delimiter of provided pattern.

But now I need something better for performance test. If it is possible, I would love to see the most universal solution, because not every user of my system is working on PostgreSQL database (few of them prefer Oracle, MySQL or SQLite), but it is not the most importatnt. But performance is - because on specific search, that function can be called even few hundreds times.

I didn't find anything about fast and easy using varchar as a table of chars and checking for occurences of delimiter (I could remember position of occurences and then create substring from first char to n'th delimiter position-1). Any ideas? Are smarter solutions?

@ EDIT: yea, I know that function in every database will be a bit different, but body of function can be very similliar or the same. Generality is not a main goal :) And sorry for that bad function working-name, I just saw it has not right meaning.

Answer 1

you can try doing something based on this:

select 
  varcharColumnName,
  INSTR(varcharColumnName,'-',1,2),
  case when INSTR(varcharColumnName,'-',1,2) <> 0
    THEN SUBSTR(varcharColumnName, 1, INSTR(varcharColumnName,'-',1,2) - 1)
    else '...'
  end
from tableName;

of course, you have to handle "else" the way you want. It works on postgres and oracle (tested), it should work on other dbms's because these are standard sql functions

//edit - as a function, however this way it's rather hard to make it cross-dbms

CREATE OR REPLACE FUNCTION find_position_delimiter(fulltext varchar, delimiter varchar, occurence integer) 
RETURNS varchar as
$BODY$

DECLARE
    result varchar := '';
    delimiterPos integer := 0;

BEGIN
    delimiterPos := INSTR(fulltext,delimiter,1,occurence);
    result := SUBSTR(fulltext, 1, delimiterPos - 1);
    RETURN result;
END;


$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
SELECT find_position_delimiter('My-Example-Awesome-String', '-', 2);