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'.
GRChandrashekarAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
use a function like this

CREATE OR REPLACE FUNCTION fncHECK
   vString    VARCHAR2 IN
RETURN BOOLEAN
IS
  vNumber    NUMBER;
  vNumber := 0;

SELECT INSTRB(colName, vString, 1, 1)  INTO vNumber
FROM yourTable
WHERE INSTRB(colName, vString, 1, 1) > 0;

IF vNumber  != 0 THEN
   RETURN True;
ELSE
   RETURN False;
END IF;


0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
This will do right ?

select * from table where column like '%A%';
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
or another version using instr :

select * from table where instr(column,'A') > 0;
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
GRChandrashekarAuthor Commented:
@ 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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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,
0
 
Amitkumar PSr. ConsultantCommented:
Hi,

Try the following.

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

Thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
slightwv (䄆 Netminder) Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.