Use string contains function in oracle SQL query

Question

I'm using an Oracle database and I want to know how can I find rows in a varchar type column where the values of that column has a string which contains some character.

I'm trying something like this (that's a simple example of what I want), but it doesn't work:

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

I also want to know if I can use a function like chr(1234) where 1234 is an ASCII code instead of the 'A' character in my example query, because in my case I want to search in my database values where the name of a person contains the character with 8211 as ASCII code.

With the query select CHR(8211) from dual; I get the special character that I want.

Example:

select p.name from   person p where  p.name contains the character chr(8211); 

Answer 1

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.