Partial search not working as expected

[hadoob024]

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!!!

[hadoob024]

Sorry.  I had a COMPLETE brain fart!  Can you please move this topic to the Oracle forums as my question is related to the Oracle DBMS?  Thanks!

[toplay]

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.

 

 

 

 

 

[hadoob024]

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()?

[gizmola]

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.