CODEWITHSUNDEEP
sqloracle-database
sri raj
sri raj

Reputation: 21

Select statement with column contains '%'

I want to select names from a table where the 'name' column contains '%' anywhere in the value. For example, I want to retrieve the name 'Approval for 20 % discount for parts'.

SELECT NAME FROM TABLE WHERE NAME ... ?

Upvotes: 1

Views: 139

Answers (4)

Gary_W
Gary_W

Reputation: 10360

Make life easy on yourselves and just use REGEXP_LIKE( )!

SQL> with tbl(name) as (
      select 'ABC' from dual
      union
      select 'E%FS' from dual
    )
    select name
    from tbl
    where regexp_like(name, '%');

NAME
----
E%FS

SQL>

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

I read the documentation mentioned by Gordon. The relevent sentence is:

An underscore (_) in the pattern matches exactly one character (as opposed to one byte in a multibyte character set) in the value

Here was my test:

select c 
from (
select 'a%be' c
from dual) d
where c like '_%'

The value a%be was returned.

While the suggestions of using instr() or length in the other two answers will lead to the correct answer, they will do so slowly. Filtering on function results simply take longer than filtering on fields.

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270331

You can use like with escape. The default is a backslash in some databases (but not in Oracle), so:

select name
from table
where name like '%\%%' ESCAPE '\'

This is standard, and works in most databases. The Oracle documentation is here.

Of course, you could also use instr():

where instr(name, '%') > 0

Upvotes: 5

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One way to do it is using replace with an empty string and checking to see if the difference in length of the original string and modified string is > 0.

select name 
from table 
where length(name) - length(replace(name,'%','')) > 0

Upvotes: 0

Related Questions