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);
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.
Source: Stackoverflow.com