Link to home
Create AccountLog in
Avatar of GRChandrashekar
GRChandrashekarFlag for India

asked on

Oracle See if a Character is in String

Hi

I have a TABLE and COLUMN. The Column stores values as A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q

What I want to do is Write a query or SP or function to return check if a character is present in this string and return true if present other wise false

Let me say for example
SELECT * FROM TABLE WHERE COLUMN " HAS VALUE 'A'.
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

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
ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Avatar of GRChandrashekar

ASKER

@ 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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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;

Open in new window