like and regexp_like

Question

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.

Answer 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');

Answer 2

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');