Oracle SQL, how can i use like operator in this way => like '%column name%'

November 9, 2023

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
from dual
)

select
*
from table
where column like '%usr_ip%'

I wanna get the result dataset of the column containers usr_ip

>Solution :

The following would generally work…

with user_input as( select 'user input' usr_ip from dual )
select * from someTable 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.