Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle See if a Character is in String

Posted on 2011-02-15
10
Medium Priority
?
461 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

618 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