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.

The answer of ADTC works fine, but I've find another solution, so I post it here if someone wants something different.

I think ADTC's solution is better, but mine's also works.

Here is the other solution I found

select p.name
from   person p
where  instr(p.name,chr(8211)) > 0; --contains the character chr(8211) 
                                    --at least 1 time

Thank you.

You used the keyword CONTAINS in your sample queries and question. CONTAINS lets you search against columns that have been indexed with an Oracle*Text full-text index.

Because these columns are full-text indexed, you can efficiently query them to search for words and phrases anywhere with the text columns without triggering a full table scan. Depending upon their usage, using LIKE or INSTR will almost always result in a full table scan.

CONTAINS is used to search for words and phrases. Although there are many options it is not appropriate if you are looking for embedded characters such as 'A' or chr(8211).

The following query will return all rows that contain the word "smith" anywhere in their text.

SELECT score(1), p.name
FROM person p
WHERE CONTAINS(p.name, 'smith', 1) > 0;

For more details see:

• How does contains() in PL-SQL work?
• Oracle SQL "contains" clause tips
• Oracle: Contains Documentation
• Oracle: Contains Operators