Oracle See if a Character is in String

This will do right ?

select * from table where column like '%A%';

or another version using instr :

select * from table where instr(column,'A') > 0;

if you want 'TRUE' to be returned, then just use that instead of * in the above queries.

select 'TRUE' result from table where instr(column,'A') > 0;

or if you want 1 to be returned, then just use it as shown below :

select 1 result from table where instr(column,'A') > 0;

1 or TRUE means it has found. If there is no data returned by the query, then it means it cannot find the character. so that is like 0 or FALSE

the function given by tigin44 can work only in plsql and not in sql because there is no boolean data type in SQL. i mean you will not be able to call that function from a sql query.

@ nav_kum_v:

This is fine

select 'TRUE' result from table where instr(column,'A') > 0;

But needs little more revision. It should return FALSE if A does not exist

try the below :

CREATE OR REPLACE FUNCTION check_and_return(mycol varchar2, search_char char) RETURN varchar2 is
ret_var    VARCHAR2(10);
begin

if instr(mycol,search_char) > 0  then
ret_var := 'TRUE';
else
   ret_var := 'FALSE';
end if;
return ret_var;
end;
/

select column, check_and_return(column,'A') result
from your_table;

Thanks,

Hi,

Try the following.

select
  case
    when instr(t.YOUR_COLUMN,'A') > 0 then 'TRUE'
    else 'FALSE'
  end result    
from
  YOUR_TABLE t;

Thanks.

you might want to look at this article:
https://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

I believe the functions above need a little work.

If you look for 'H' and the input string has 'G,H1,I', they will return true.

Check out the test below.

drop table tab1 purge;
create table tab1(col1 varchar2(40));
insert into tab1 values('A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q');
insert into tab1 values('A,B,C,D,E,F,G,H1,I,J,K,L,M,N,O,P,Q');
commit;


create or replace function myFunc(inCol in varchar2, inVal in varchar2) return varchar2 is
begin
	return  case when instr(',' || inCol || ',', ',' || inVal || ',') > 0 then 'TRUE' else 'FALSE' end;
end;
/

show errors

select col1, myFunc(col1,'H') from tab1;

Select allOpen in new window