CODEWITHSUNDEEP
oracle-database
HiDayurie Dave
HiDayurie Dave

Reputation: 1807

Oracle query data where column value with comma to check the value contains or not

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

Example I have data below:

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

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

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');

How to do the correct query?

Upvotes: 0

Views: 935

Answers (3)

apomene
apomene

Reputation: 14389

use like instead:

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

The above is a 'dirty fix' on a bad table use. You should avoid storing concatenated strings on the same entry. Also take into account that the above query will be valid for a subset of given IPs. Above IP will guarantee you have a correct result, however using an IP like e.g. 192.168.183.2, would might bring unwanted entries, like e,g 192.16.183.21,...

Upvotes: -1

Goran Stefanović
Goran Stefanović

Reputation: 328

If you are looking for exact matches you can use like condition or instr function, but you will need to concatenate commas on both sides, as in:

select profileid
  from wa_bt_tbl_profile p
 where ', ' || p.allowed_ip_address || ',' like '%, 192.168.183.28,%'

[Edit] If you do not use commas when comparing IP addresses you may get unwanted results - for instance, if you used address 10.10.10.10 for comparison, without commas valid results would also be 110.10.10.10, 10.10.10.101, 110.10.10.101, etc.

I suggest you remove space between comma and IP address - it just makes the code harder to understand.

Upvotes: 1

MT0
MT0

Reputation: 168720

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.

Upvotes: 2

Related Questions