Search code examples
sqlregexoracle-databaseoracle11gregexp-like

like and regexp_like

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.

Solution

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