Solved

Oracle See if a Character is in String

Posted on 2011-02-15
10
435 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
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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 20

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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34905009
0
 
LVL 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now