Use string contains function in oracle SQL query

48
votes

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);
3
sqloracle

3 Answers

90
votes

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.

32
votes

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.

2
votes

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: