Home » SQL & PL/SQL » SQL & PL/SQL » Pattern matching
Pattern matching [message #35936] Thu, 25 October 2001 00:51 Go to next message
Amit
Messages: 166
Registered: February 1999
Senior Member
Hi All,
I am having one table lets say 'A' in that there are several columns.Out of which one is empname.now this column has values like:'John', 'Jhonty', 'Ranu','Rinku','Dolly','%Balak'.
Now how can i retrieve the row in which empname contains '%' in that.I have tried using it with like operator but no use..Pls.Let me know if anybody knows this.
Thanx in advance.

----------------------------------------------------------------------
Re: Pattern matching [message #35939 is a reply to message #35936] Thu, 25 October 2001 03:19 Go to previous messageGo to next message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
See the result below change the table name and column name , it's working fine on Oracle 8.1.6

SQL> ed
Wrote file afiedt.buf

1 select b from c
2* where b like '%%%'
SQL> /

B
----------
%123

----------------------------------------------------------------------
Re: Pattern matching [message #35942 is a reply to message #35939] Thu, 25 October 2001 03:33 Go to previous messageGo to next message
Amit
Messages: 166
Registered: February 1999
Senior Member
Thanx Satish,But it is giving me all the rows.I am also having the same oracle version.But it is not working as required.

----------------------------------------------------------------------
Re: Pattern matching [message #35945 is a reply to message #35936] Thu, 25 October 2001 03:48 Go to previous messageGo to next message
Hans
Messages: 42
Registered: September 2000
Member
drop table t;
create table t (
   c  varchar2(30)   
);
 
insert into t values ('abc');
insert into t values ('%abc');
insert into t values ('abc%');
insert into t values ('%abc');
insert into t values ('a%bc%');
insert into t values (null);
 
select * from t
   where  c like '%%%' escape '';
   
or
 
select * from t
   where  instr(c,'%') > 0;


----------------------------------------------------------------------
Re: Pattern matching [message #35947 is a reply to message #35936] Thu, 25 October 2001 04:51 Go to previous message
Milan Kumar Barui
Messages: 16
Registered: October 2001
Junior Member
Amit, Satish is wrong, Hans has given very good solution.
Thanks
Milan

----------------------------------------------------------------------
Previous Topic: propblem with array
Next Topic: Please advice where can I find some material/questions to..
Goto Forum:
  


Current Time: Sun Mar 29 09:33:00 CDT 2020