ibaneight ibaneight - 1 month ago 16
SQL Question

Use string contains function in oracle SQL query

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 Source

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.