什么是Oracle中MySQL的Find_in_set的替代方法 [英] What is alternative of Find_in_set of mysql in Oracle

问题描述

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

我想要在oracle中得到相同的结果.在oracle中设置find_in的替代选项是什么?

此处ComExgRateDetailLog.NotificationMinute包含类似'0,15,30,45'的值，因此查询应类似于

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

解决方案

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

为了在oracle中实现此目的，请使用INSTR函数，但INSTR与FIND_IN_SET并不完全相同. INSTR将逗号，空格，字符串中的任何内容都视为字符.

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

您可以在此处了解有关INSTR的信息. /p>

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 want same result in oracle. What is alternative option of find_in set in oracle?

Here ComExgRateDetailLog.NotificationMinute contains 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

解决方案

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

In order to achieve this in oracle use the INSTR function, but INSTR is not exactly the same as FIND_IN_SET. INSTR considers 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 read about INSTR here.

这篇关于什么是Oracle中MySQL的Find_in_set的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！