What is alternative of Find_in_set of mysql in Oracle

Question

select   CASE
          WHEN ComExgRateDetailLog.NotificationMinute = '*'
          THEN
             1
          ELSE
             IF(FIND_IN_SET(
                   CAST(
                      DATE_FORMAT(
                         DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00'),
                         '%i') AS SIGNED),
                   ComExgRateDetailLog.NotificationMinute) > 0,
                1,
                0)
       END

       From ComExgRateDetailLog

I need the same result in oracle. What is the alternative option of find_in set in oracle?

Here ComExgRateDetailLog.NotificationMinute holds value like '0,15,30,45' So query should be like

select   CASE
          WHEN ComExgRateDetailLog.NotificationMinute = '*'
          THEN
             1
          ELSE
             IF(FIND_IN_SET(
                   CAST(
                      DATE_FORMAT(
                         DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00'),
                         '%i') AS SIGNED),
                   '0,15,20,45') > 0,
                1,
                0)
       END

       From ComExgRateDetailLog

In order to accomplish this in oracle, you have to use the INSTR function, but INSTR is not accurately the same as FIND_IN_SET. INSTR holds a comma, space, anything inside a string as a character.

You can go through about INSTR from here.

Answer 1

select FIND_IN_SET('15', '0,15,20,45') from dual would return 2

In order to accomplish this in oracle, you have to use the INSTR function, but INSTR is not accurately the same as FIND_IN_SET. INSTR holds a comma, space, anything inside a string as a character.

SELECT INSTR ('0,15,20,45', '15',1,1) FROM dual would return 3

You can go through about INSTR from here.

Willing to Learn SQL? Come and Join the SQL Certification course by Intellipaat.