Comma-separated filter

[end3r]

Hi,

I have a select statement and I need to find a way to apply a filter to it:

select * from my_table where my_table.field_1 like :filter_item

The real problem is that filter_item can be a string, a string that includes '%' or a comma-separated string that can include wildcard like this:

abc, abgf%fri, e%ij%fd, abcd

The best way would be to solve this without creating any special package/function, etc. But if there is no other way then any solution is welcome.

Thanks,
Ender

[debasisdas]

so, what is the problem ?

[end3r]

The problem is that the :filter_item = 'abc, abgf%fri, e%ij%fd, abcd'.
So the select will look like:

SELECT * FROM my_table WHERE my_table.field_1 LIKE 'abc, abgf%fri, e%ij%fd, abcd'

I need to split this and have the following functionality:

SELECT * FROM my_table 
WHERE my_table.field_1 LIKE 'abc'
OR my_table.field_1 LIKE 'abgf%fri'
OR my_table.field_1 LIKE 'e%ij%fd'
OR my_table.field_1 LIKE 'abcd'

Do you have a solution for this ?

[debasisdas]

you have to create a procedure / function for that.

[end3r]

I think there is a solution using the REGEXP_LIKE function. The only problem is that I am not sure what regular expression should be used.

[Brillig]

Generally filterItem is being passed in from code (PHP, JSP, C#, etc.).

This would be the place to break that out into an array, separating on commas, then either reparse into separate "OR"s or pass in as separate parameters.

I'm guessing you're doing some sort of search on keywords?

[end3r]

Hy,

I finally found the solution, so in case anyone encounters the same problem, here is the condition for searching an IP_ADDRESS column by a comma-separated list of IPs:

-- YOUR CODE....
AND REGEXP_LIKE(IP_ADDRESS, REPLACE(REPLACE(REPLACE(REPLACE(NVL(:IP_ADDRESS_LIST, '%.%.%.%'), ', ', '$|'), ',', '$|'), '%', '[0-9]{1,3}'), '.', '\.') || '$' )

Best Regards,
Ender