Counting occurrences in Amazon Redshift strings

Question

I need to get the first occurrence of one of those characters ("1" , "2" or "3") and also count how many times a certain character ("s") occurred in the string before them.

I made this query:

SELECT
  distinct c1,
  SUBSTRING( c1, REGEXP_INSTR ( c1, '[123]+' ) , 1) as First_123 , 
  REGEXP_INSTR (  SUBSTRING( c1, 1, REGEXP_INSTR ( c1, '[123]+' )) , '[s]') as NumberOfS
FROM table

• The first column is the string
• The second column is which number occurs first (1 2 or 3)
• The third column is the number of 's' characters in the string before (1 2 or 3) occurs.

The column counting the number of 's' is only returning 0 when there at least one 's' before 1, 2 or 3 and 1 if there is more than 1 's'.

How can I make it count properly and also count all 's' characters when 1 2 or 3 are not found in the string?

Answer 1

select  c1
       ,regexp_substr (c1,'[1-3]')  as first_123 
       ,regexp_count  (c1,'s[1-3]') as single_s_before_123
       ,regexp_count  (c1,'s')      as all_s

from    table

P.s. I currently don't have access to amazon-redshift