find nth position of a specific character in a string in Postgres

Question

I am running Postgres 9.6

I have a list of various class codes. Here is:

'What I have' And 'What I Want'

what I have --> what I want.

Equip:Mold         --> Equip:Mold
Raw:Resin:TPA      --> Raw:Resin 
FG                 --> FG
...

My strategy to accomplish this is to write a user defined function that will find the character count 2nd ':' in my list then use the LEFT function with a LEFT('Raw:Resin:TPA',nthpositionget('Raw:Resin:TPA',':',2))

I tried using the following question to no avail. Postgres: extract text up to the Nth Character in a String

Answer 1

This overall problem is best handled with regexp_replace():

select regexp_replace('Raw:Resin:TPA', '(^.*:.*):', '\1');
 regexp_replace 
----------------
 Raw:ResinTPA
(1 row)

select regexp_replace('Equip:Mold', '(^.*:.*):', '\1');
 regexp_replace 
----------------
 Equip:Mold
(1 row)

select regexp_replace('FG', '(^.*:.*):', '\1');
 regexp_replace 
----------------
 FG
(1 row)

If you want something that finds the nth occurrence of a substring, then something like this could be made into a function:

with invar as (
  select 'Raw:Resin:TPA' as a, ':' as d
)
select case
         when length(array_to_string((string_to_array(a, d))[1:2], d)) = length(a) then -1
         else length(array_to_string((string_to_array(a, d))[1:2], d)) + 1
       end
 from invar;