Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> backwards LIKE matching

backwards LIKE matching

From: Ariel <asnews_at_dsgml.com>
Date: Thu, 07 Sep 2006 05:02:03 -0400
Message-ID: <S7SdnfVCdbeWfWLZnZ2dnUVZ_oudnZ2d@comcast.com>


I'm reversing the normal order of LIKE, with the column on the right side:

SELECT domain
FROM tbl_domain
WHERE 'com.hotmail.subdomain' LIKE domain || '%'

This works, but will this query use an index? If not, is there any way I can optimize this? I have flexibility in how I will store the data (as you can see I reversed the domain sections for this test).

If I store .hotmail.com (stored here as com.hotmail.) in the database, I want to be able to match subdomain.hotmail.com, etc. Or if I store .mail.google.com in the DB, I want to match subdomain.mail.google.com (but if .mail.google.com is stored in the DB, then .google.com should NOT match).

The query as written, technically should be index search able - it just needs to look for values starting with c then work through each letter of the rest of com.hotmail.subdomain and only look at values starting with each prefix, but I don't know if oracle actually does that.

Perhaps a totally different storage method?

        -Ariel Received on Thu Sep 07 2006 - 04:02:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US