What is the best way to parameterize a LIKE query?

Question

I'm trying to implement a simple search, using LIKE in my SQL statement:

        Using cmd As New OracleCommand
           cmd.Connection = conn
           cmd.CommandType = CommandType.Text
           cmd.CommandText = "select * from TABLE where FIELD like '%:text%'"
           cmd.Parameters.AddWithValue("text", searchValue)
           ...
        End Using

This doesn't work - what is the best way to parameterize the search value?

Answer 1

update: my bad, if you are using oracle driver, see this link for help. this depends on which driver you use for db access, in case of oledb you need to do following here is corrected code:

Using cmd As New OracleCommand
           cmd.Connection = conn
           cmd.CommandType = CommandType.Text
           cmd.CommandText = "select * from TABLE where FIELD like ?"
           cmd.Parameters.AddWithValue("@text", '%'+searchValue+'%')
           ...
        End Using

in case of oracle driver you need to do following:

Using cmd As New OracleCommand
           cmd.Connection = conn
           cmd.CommandType = CommandType.Text
           cmd.CommandText = "select * from TABLE where FIELD like :text"
           cmd.Parameters.AddWithValue(":text", '%'+searchValue+'%')
           ...
        End Using

Answer 2

select * from TABLE where FIELD like ('%' || :text || '%')

Answer 3

In cases like this I prefer this syntax:

select * from TABLE where INSTR(FIELD, :text) > 0