Jump to content

Partial search not working as expected


Recommended Posts

Here's my problem, but first, some relevant info.  Here's my first table (SER_CONTEST):

 

ID TYPE_OF_USER

--------------------------------------------------------------------

51 SER_JOB_TYPE_PROMOTER

69 SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_PROMOTER

46 -

 

 

Here's my second table (SER_ENITY_USER):

 

ID INTERESTED_POSITIONS

------------------------------------------------------------------------

141 SER_JOB_TYPE_BARBACK/BUSSER/RUNNER

 

 

Here's my query:

 

SELECT UNIQUE SER_CONTEST.ID

FROM SER_CONTEST, SER_ENITY_USER

WHERE DRAWING_POINT >= SYSDATE AND

(SER_CONTEST.TYPE_OF_USER LIKE SER_ENITY_USER.INTERESTED_POSITIONS || '%' OR SER_ENITY_USER.INTERESTED_POSITIONS LIKE SER_CONTEST.TYPE_OF_USER || '%' )

 

 

This query is pulling back 2 records (contest ID's of 51 and 69) with the current data in the tables, even though it should pull back 0 records for this particular query. Only when I change my second table (SER_ENITY_USER) to this should it pull back 2 records:

 

ID INTERESTED_POSITIONS

------------------------------------------------------------------------

141 SER_JOB_TYPE_PROMOTER

 

 

Is there a typo on my part? I'm not sure why it's not working.  I tried various combinations of wildcards and even using the soundex() function, but none of them work (I'm guessing soundex() isn't working because each db entry starts with "SER_JOB_TYPE_".  Any thoughts?  Thanks in advance!!!

Link to comment
Share on other sites

I've forgotten everything about Oracle now, but with the "LIKE" you usually use the "%" next to the value or column.

 

Examples:

 

column_name LIKE "%nuts"    # Finds everything ending with nuts...i.e. donuts or lug nuts

 

column_name LIKE "bicycle%"  # Finds everything starting with bicycle...i.e. bicycle shop

 

column_name LIKE CONCAT('%', other_column_name, '%')  # Checks if column_name contains anywhere the value of other_column_name

 

So I doubt the syntax you have of "|| % OR" will work. Check Oracle site/manual for proper syntax.

 

Good luck.

 

 

 

 

 

Link to comment
Share on other sites

I've been working on it all day, and here's where I am.  I tried something similar to that, but here's the problem that I was having:

 

Here's my second table (SER_ENITY_USER) now:

 

ID INTERESTED_POSITIONS

------------------------------------------------------------------------

141 SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_BOUNCER/SECURITY

 

 

Now if I run my updated query:

SELECT UNIQUE SER_CONTEST.ID

FROM SER_CONTEST, SER_ENITY_USER

WHERE DRAWING_POINT >= SYSDATE AND

SER_ENITY_USER.ENITY_ID = '141' AND

('%' || SER_CONTEST.TYPE_OF_USER || '%' LIKE '%' || SER_ENITY_USER.INTERESTED_POSITIONS || '%' OR '%' || SER_ENITY_USER.INTERESTED_POSITIONS || '%' LIKE '%' || SER_CONTEST.TYPE_OF_USER || '%')

 

 

With this query, it only pulls back the record with ID of 51, but it won't pull back the record with ID of 69.

 

I also noticed that if I change my table (SER_CONTEST) to the following:

 

ID TYPE_OF_USER

-----------------------------------------------

51 SER_JOB_TYPE_PROMOTER

69 SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_OTHER

46 -

 

 

Then my query pulls back the proper 2 records (with ID's of 51 and 69). Did I mess up on my query, or is this a different issue that hasn't been taken into account? Basically I want to find if any of the items for the value of a field appears in any items of the items for the value of the other field.

 

Like where if TYPE_OF_USER equals:

SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_BARBACK/BUSSER/RUNNER

 

And where if INTERESTED_POSITIONS equals:

SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_OTHER

 

This produces a match.

 

Or like where if TYPE_OF_USER equals:

SER_JOB_TYPE_BARBACK/BUSSER/RUNNER:SER_JOB_TYPE_PROMOTER:SER_JOB_TYPE_BARTENDER

 

And where if INTERESTED_POSITIONS equals:

SER_JOB_TYPE_KITCHEN_STAFF:SER_JOB_TYPE_BARTENDER:SER_JOB_TYPE_OTHER

 

 

This should also produce a match.  I tried using soundex(), but the whole "SER_JOB_TYPE_" part keeps producing a match.  Is there anyway to disregard that part when doing soundex()?

Link to comment
Share on other sites

  • 2 weeks later...

Not one but two tables that should be many to many resolvers, and you wonder why you're struggling? 

 

I hope you realize that even if you get these queries working, Oracle can not use indexes at all with the approach that has been taken in storing this data. With that said, the INSTR or REGEXP_LIKE functions might be of interest to you.

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.