Use String Contains Function in Oracle SQL Query

Use string contains function in oracle SQL query

By lines I assume you mean rows in the table person. What you're looking for is:

select p.name
from   person p
where  p.name LIKE '%A%'; --contains the character 'A'

The above is case sensitive. For a case insensitive search, you can do:

select p.name
from   person p
where  UPPER(p.name) LIKE '%A%'; --contains the character 'A' or 'a'

For the special character, you can do:

select p.name
from   person p
where  p.name LIKE '%'||chr(8211)||'%'; --contains the character chr(8211)

The LIKE operator matches a pattern. The syntax of this command is described in detail in the Oracle documentation. You will mostly use the % sign as it means match zero or more characters.

CONTAINS in Oracle 11.2

You can use LIKE as follows:

select * 
  from cars
 inner join customers on customers.as_id = cars.as_id
 where cars.type = 'AUDI' -- You can use = here
   and request like '%' || customers.name || '%';

Note: contains clause is used for finding specific strings within an Oracle Text index. it can not be applied on normal columns.

operation LIKE in PL/SQL with string that contain _ symbol

I think you want:

WHERE 'TRY_1235' LIKE col || '%'

The column can be the pattern.

Note that this will match 'TRYST' because _ is a wildcard character. Perhaps the simplest solution is REGEXP_LIKE() -- assuming there are no other unusual characters:

WHERE REGEXP_LIKE('TRY_12345', '^' || col)

Or just escape the _:

WHERE 'TRY_12345' LIKE REPLACE(col, '_', '\_') || '%'

Check for a substring in a string in Oracle without LIKE

I'm guessing the reason you're asking is performance?
There's the instr function. But that's likely to work pretty much the same behind the scenes.

Maybe you could look into full text search.

As last resorts you'd be looking at caching or precomputed columns/an indexed view.

How does contains() in PL-SQL work?

Contains is used on text fields that have a 'CONTEXT Index', which indexes a text field for searching. The standard usage is like this (using the score operator to display what is returned from the contains clause based on the 1 in contains matching the 1 in score):

SELECT score(1), value
FROM table_name
WHERE CONTAINS(textField, 'searchString', 1) > 0;

For data like this in table table_name

value  |  textField
-------|-----------------------------------------------
A      |   'Here is searchString.  searchString again.'
B      |   'Another string'
C      |   'Just one searchString'

That query would return

2 A
1 C

So contains is similiar to like, but will count how many times a string occurs in a text field. I couldn't find a resource using Contains the way it is used in the query you posted, but I think that would return rows where dFullText has at least one instance of car in it, or the equivalent of this sql:

Select * from blabla where dFullText like "%car%"

Here is another source.

---

Related Topics