1

For like we have %. for ex: if we give ad% it ll fetch all the records which starts with ad but i should use regexp_like. for regexp_like what can be used so that it acts as % for like. i cant use ^ad because from UI we ll give something like ad* to fetch.

Before query:select * from employee where fname like 'pr%';

Present query:select * from employee where regexp_like(fname ,'pr+');

for present query i m getting the values which contains pr but i want to get values which starts with pr.

Testdata: if pr* is given then i should get program etc i.e the value which starts with pr.

Alén
  • 125
  • 2
  • 9
  • 1
    could you add your query so we can see what you are trying to do? also maybe some test data and expected result – Jeremy C. Jun 23 '15 at 06:30
  • is my question clear now? – Alén Jun 23 '15 at 07:23
  • first of all you are using regexp wrong, the correct syntax afaik is "...where regexp_like(fname, 'pr+')" and I think the wildcard character for regexp_like is a period "." so you would need 'pr.' – Jeremy C. Jun 23 '15 at 07:28

2 Answers2

1

Try this one:

SELECT * 
FROM employee 
WHERE REGEXP_LIKE (fname, '^pr(*)');

Fiddle

This one also seems to work as far as I can tell:

SELECT * 
FROM employee 
WHERE REGEXP_LIKE (fname, '^pr.');

Or another one that works:

SELECT *
FROM employee
WHERE regexp_like(fname,'^pr');
Jeremy C.
  • 2,405
  • 1
  • 12
  • 28
  • that's what we're here for, no problem – Jeremy C. Jun 23 '15 at 08:13
  • hey jeremy i m trying SELECT * FROM mytable WHERE regexp_like(fname,'n$'); to fetch the values which ends with n but $ is not working ..:( can u help me... – Alén Jun 23 '15 at 11:42
  • should be something like "regexp_like(fname,'(*)n$');" , actually n$ should work to, you might need to trim your fname if it's not a varchar field (if it's a normal char(20) or something) – Jeremy C. Jun 23 '15 at 13:11
  • @Alén try this: "WHERE regexp_like(trim(fname),'n$');" – Jeremy C. Jun 23 '15 at 13:18
0

Check below link regexp_like -

http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions018.htm

If still not able to reslove your issue then as pointed by Jeremy share test data and expected result. That will help to reslove.

Check below example

create table a 
(b varchar2(150));

insert into a values ('Pravin');

insert into a values ('TestPravin');

select * from a where REGEXP_LIKE (B, '^Pra');
Pravin Satav
  • 702
  • 5
  • 17
  • 36