Find each position of same characters in a string through SQL query

user_1534183 0
Viewed 962

Below is my data set

Year        Month       Holiday list
----------------------------------------------------------
2007        1           WWHHWWWWWHHWWWWWHHWWWWWHHWWWWWH
2008        4           HWWWWWHHWWWWWHHWWWWWHHWWWWWHHWW

I want to write SQL query to get below output wherein the position of each "H" is displayed.

Year        Month       Holiday list
-----------------------------------------------------
2007        1           3 4 10 11 18 19 25 26 31
2008        4           1 7 8 14 15 21 22 28 29

The INSTR function in Oracle returns the position of first character. So, I cannot use it.

I could write a Function and pass the Holiday List column value to it. Loop through the string and find the position of each "H". But, I want to achieve this through plain SQL select query.

How to find each position of same characters in a string through SQL query in Oracle database?

oracle sql
2 Answers

Something like this might do it. You can use the INSTRfunction and a hierarchical query to go through the string and then use the LISTAGG analytic function to concatenate the results.

WITH data AS (
  SELECT 2007 year, 1 month, 'WWHHWWWWWHHWWWWWHHWWWWWHHWWWWWH' holiday_list FROM DUAL
  UNION
  SELECT 2008 year, 4 month, 'HWWWWWHHWWWWWHHWWWWWHHWWWWWHHWW' holiday_list FROM DUAL)
SELECT year, month, (SELECT LISTAGG(INSTR(holiday_list,'H',1,LEVEL),' ') WITHIN GROUP (ORDER BY LEVEL)
FROM DUAL
CONNECT BY LEVEL < INSTR(holiday_list,'H',1,LEVEL)) S
FROM data;
user_22442860

Well you can get a list, see the dbfiddle here

SELECT id, INSTR(some, 'H', 1, c.no) AS Pos
FROM tst CROSS JOIN consecutive c
WHERE INSTR(some, 'H', 1, c.no) > 0

This gives you a list of the days with an H. You could then try to transpose this list. But be warned, there is an intentional CROSS JOIN there, so for every row in your data you will get up to 31 rows with this statement. Like Tim Biegeleisen said, it may be better to use an UDF for this.

user_48003110
Related