Solved

Oracle See if a Character is in String

Posted on 2011-02-15
10
446 Views
Last Modified: 2012-05-11
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'.
0
Comment
Question by:GRChandrashekar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34904220
This will do right ?

select * from table where column like '%A%';
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34904225
or another version using instr :

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

Expert Comment

by:Naveen Kumar
ID: 34904228
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 34904257
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34904275
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
 

Author Comment

by:GRChandrashekar
ID: 34904348
@ 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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34904371
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
 
LVL 21

Expert Comment

by:Amitkumar Panchal
ID: 34904941
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34906677
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question