CODEWITHSUNDEEP
sqloracle-database
ibaneight
ibaneight

Reputation: 1445

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

Upvotes: 74

Views: 449381

Answers (4)

Sarath Subramanian
Sarath Subramanian

Reputation: 21271

Just in case you need to find if a column has any values that have character in it, you can use regexp_like. The first parameter is the column name to be checked and the second parameter is the regular expression.

If the below sql returns count greater than zero, that means there are some row(s) in which there is 1 or more character in it.

SELECT COUNT(*) 
FROM TABLE_NAME 
WHERE regexp_like (COLUMN_NAME, '[^0-9]')

Upvotes: 2

ibaneight
ibaneight

Reputation: 1445

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

Upvotes: 44

AnthonyVO
AnthonyVO

Reputation: 3973

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:

Upvotes: 8

ADTC
ADTC

Reputation: 10086

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.

Upvotes: 127

Related Questions