How to get all position indexes of a specific char in a string?

Question

I'm using postgres and PgAdmin3. I'm stuck and I do not even know where to start!

I have a column which have the following format:

tvd_unit
YYNNNNNNNNNNNNNYYNNNNN

The column contain 22 positions, the positions represents whether the behavior is active or not in a particular business unit, through yes or no (Y or N)

In the example above, the parameter is active only in 1, 2, 16 and 17 units. And the rest is off, I need to develop a query to return each number of unit where the parameter is active, in the above example, I need to return:

1, 2, 16, 17

I honestly have no idea how to start getting the number of units, and I need help.

Check this question. It may help you. https://stackoverflow.com/questions/34495881/how-get-all-positions-in-a-field-in-postgresql — mkRabbani, Jun 12 '19 at 17:38
Thanksssssssssssssssssssss! Its worked perfectly! I just only changed to return in 000 format — Nycolas Merino, Jun 12 '19 at 18:19
Shawn.X the comment of mkRabbany helped me a lot! And is a solution. — Nycolas Merino, Jun 13 '19 at 13:06

score 0 · Answer 1 · answered Jun 12 '19 at 18:05

You can use generate_series and substr to grab each element from your string:

with data as (select 'YYNNNNNNNNNNNNNYYNNNNN' as tvd_unit)
select position,
       substr(tvd_unit, position, 1) as active
FROM data, 
     generate_series(1, length(tvd_unit), 1) g(position)
WHERE substr(tvd_unit, position, 1) = 'Y';
 position | active
----------+--------
        1 | Y
        2 | Y
       16 | Y
       17 | Y
(4 rows)

Shawn.X · Answer 2 · 2019-06-13T01:52:20.580

The steps to solve the problem as below. You can first construct an array of your string:

postgres=# select string_to_array(regexp_replace(replace(replace('YYNNNNNNNNNNNNNYYNNNNN','Y','Y,'),'N','N,'),',$',''),',') as arr;
                      arr                      
-----------------------------------------------
 {Y,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,Y,Y,N,N,N,N,N}
(1 row)

And then convert the array into multi rows and give each row a sort number:

postgres=# with tmp as (                                                                                                           
select string_to_array(regexp_replace(replace(replace('YYNNNNNNNNNNNNNYYNNNNN','Y','Y,'),'N','N,'),',$',''),',') as arr
)
select
    unnest(arr) as letter,
    generate_series(1,array_length(arr,1),1) as sort
from
    tmp;
 letter | sort 
--------+------
 Y      |    1
 Y      |    2
 N      |    3
 N      |    4
 N      |    5
 N      |    6
 N      |    7
 N      |    8
 N      |    9
 N      |   10
 N      |   11
 N      |   12
 N      |   13
 N      |   14
 N      |   15
 Y      |   16
 Y      |   17
 N      |   18
 N      |   19
 N      |   20
 N      |   21
 N      |   22
(22 rows)

The final complete SQL you want:

postgres=# with tmp as (                                                                                                           
select string_to_array(regexp_replace(replace(replace('YYNNNNNNNNNNNNNYYNNNNN','Y','Y,'),'N','N,'),',$',''),',') as arr
)
,tmp_2 as (
select
    unnest(arr) as letter,
    generate_series(1,array_length(arr,1),1) as sort
from
    tmp
)
select * from tmp_2 where letter = 'Y';
 letter | sort 
--------+------
 Y      |    1
 Y      |    2
 Y      |   16
 Y      |   17
(4 rows)

postgres=# with tmp as (
select string_to_array(regexp_replace(replace(replace('YYNNNNNNNNNNNNNYYNNNNN','Y','Y,'),'N','N,'),',$',''),',') as arr
)
,tmp_2 as (
select
    unnest(arr) as letter,
    generate_series(1,array_length(arr,1),1) as sort
from
    tmp
)
select letter,array_to_string(array_agg(sort),',') as result from tmp_2 where letter = 'Y' group by letter;
 letter |  result   
--------+-----------
 Y      | 1,2,16,17
(1 row)

How to get all position indexes of a specific char in a string?

2 Answers2