I wanna search for certain data with the a certain pattern. But I would like to do it as an user-input function, search for the pattern that i put in.
with user_input as(
select 'user input' usr_ip
where column like '%usr_ip%'
I wanna get the result dataset of the column containers usr_ip
The following would generally work...
with user_input as( select 'user input' usr_ip from dual )
select * from someTable, user_input where someColumn like '%'||user_input.usr_ip||'%'
But if your real question is how to get user input into a sql statement, well, that is not something to be done in SQL. There is no SQL statement or function to get data from the user directly. Mechanisms like host variables or dynamic sql can be used in the calling program to provide user input to the database.