Oracle查询数据，其中列值用逗号检查值是否包含 [英] Oracle query data where column value with comma to check the value contains or not

问题描述

我想查询数据以检查至少包含1个IP地址.

I would like to query data to check at least contains 1 IP Address.

示例我的数据如下:

PROFILEID   PROFILE_NAME        ACTIVE  DATEADDED               ADDEDBY LOCATIONID_FK   ALLOWED_IP_ADDRESS
PF0001      Normal Working Day  Y       9/30/2017 5:53:39 PM    US0001  LC0001          192.168.183.205, 192.168.183.28
PF0004      Ramadhan            N       10/12/2017 10:38:02 AM  US0001  LC0003  
PF0002      Ramadhan            N       9/30/2017 5:55:50 PM    US0001  LC0001          192.168.183.205, 192.168.183.28
PF0003      Normal Working Day  Y       10/3/2017 5:23:05 PM    US0001  LC0003          192.168.184.20, 192.168.184.15

如您所见，列ALLOWED_IP_ADDRESS的IP带有更多逗号.

As you can see column ALLOWED_IP_ADDRESS has more ip with comma.

现在我要检查查询ALLOWED_IP_ADDRESS IN ('192.168.183.28')的位置，但没有结果.

And now I want to check with query where ALLOWED_IP_ADDRESS IN ('192.168.183.28'), but got no result.

SELECT PROFILEID FROM WA_BT_TBL_PROFILE P WHERE P.ALLOWED_IP_ADDRESS IN ('192.168.183.28');

如何进行正确的查询?

推荐答案

您需要检查由定界符包围的子字符串:

You need to check for a sub-string surrounded by the delimiters:

SELECT PROFILEID
FROM   WA_BT_TBL_PROFILE P
WHERE  ', ' || P.ALLOWED_IP_ADDRESS || ', ' LIKE '%, 192.168.183.28, %';

但是，更好的方法是更改​​数据库表，以使您不会在一个值中存储多个项目:

However, a better way would be to change your database table so that you are not storing multiple items in one value:

CREATE TABLE Allowed_IP_Addresses(
  PROFILEID          VARCHAR2(20)
                     CONSTRAINT AllowIP__ProfileID__FK REFERENCES WA_BT_TBL_PROFILE( PROFILEID ),
  CLASSA             NUMBER(3,0),
  CLASSB             NUMBER(3,0),
  CLASSC             NUMBER(3,0),
  CLASSD             NUMBER(3,0),
  IP_ADDRESS         VARCHAR2(15)
                     GENERATED ALWAYS AS (CLASSA||'.'||CLASSB||'.'||CLASSC||'.'||CLASSD) VIRTUAL,
  CONSTRAINT AllowIP__P_A_B_C_D__PK PRIMARY KEY ( PROFILEID, CLASSA, CLASSB, CLASSC, CLASSD )
);

然后，您可以单独存储值(并轻松搜索子网范围)，然后根据需要将其加入配置文件表.

Then you can store the values individually (and easily search for sub-net ranges) and join it to the profile table as needed.

这篇关于Oracle查询数据，其中列值用逗号检查值是否包含的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！