trunc_on_nth_occurrence_of_string.sql

CREATE or replace FUNCTION trunc_on_nth_occurrence_of_string(
                                                   _original_string text
                                                 , _search_string   TEXT
                                                 , _nth_occurrence  int
                                                  ) RETURNS TEXT LANGUAGE sql AS $$
    /*
    ==================================================
    Author:     dvandenbosch
    Created At: 7/9/2020
    Where Used: https://stackoverflow.com/questions/62801797/find-nth-position-of-a-specific-character-in-a-string-in-postgres/62802753#62802753
    ==================================================
    */
with invar as (
    select _original_string::TEXT as a, _search_string::TEXT as d
              )
select     LEFT(_original_string
                  , CASE
                    WHEN length(array_to_string((string_to_array(a, d))[1:_nth_occurrence], d)) = length(a) THEN 5000
                    ELSE length(array_to_string((string_to_array(a, d))[1:_nth_occurrence], d))
                    END
               )
from invar;
$$;