Link to home
Start Free TrialLog in
Avatar of brianmfalls
brianmfallsFlag for United States of America

asked on

Oracle Select Where String (sans hyphens) is like String

I need to figure out how to apply a regular expression to a query so that the string passed in is compared, dbData LIKE (%passedInString%), to the string in the database, but where the stings are both devoid of hyphens.

Example of what is in the database: DR-9JPE-P-77-7733
Examples of what might be passed in that would return a match: 7777333, P77, 9JPEP-77, etc.

Is it even possible?  Help me out!  :)  And thanks in advance!

A basic, albeit wrong version, of what I am trying to do:
SELECT *
FROM aTable
WHERE REGEXP(INDATABASE) LIKE (%REGEXP(PASSEDIN)%)

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Given that requirement I would replace all dashes with nothing and do an instr:
select * from table where instr(replace(passedin,'-'),replace(INDATABASE,'-')) > 0;

If there is an index on INDATABASE then it will be ignored.  If so, we can try some alternatives.  Easiest is a function-based index.
Avatar of brianmfalls

ASKER

So, to be literal, would it look like this?

SELECT *
FROM tableName
WHERE instr(replace('9JPEP-77','-'),replace(columnName,'-')) > 0;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect.  Thanks!