Use string contains function in oracle SQL query
SqlOracleSql Problem Overview
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);
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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.
Solution 3 - Sql
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: