Forum Stats

  • 3,837,089 Users
  • 2,262,225 Discussions
  • 7,900,202 Comments

Discussions

Do a search in several columns

huber
huber Member Posts: 91
edited Sep 3, 2008 2:51PM in SQL & PL/SQL
Hi,

I have a table CITY with the columns ID, NAME, DESCRIPTION and I have another table with the columns ID, FIRST_NAME, LAST_NAME, CITY_ID and I have the Table View CITIZEN which represents the columns CITY.NAME, PERSON.FIRST_NAME and PERSON.LAST_NAME.

The client is able to enter several words iin one inputText field and click the search button afterwards. A search query could look like this: Springfield + Simpson.

My query is supposed to do the following scenario. Search in PERSON.FIRST_NAME for Springfield and Simpson, search in PERSON.LAST_NAME for Springfield and Simpson, search in CITY.NAME for Springfield and Simpson and search in CITY.DESCRIPTION for Springfield and Simpson. The query shall return all the CITIZENS that contain the 2 words in any of the 4 columns.

I tried creating a index und using the sql-contains clause, but this didn't bring me anywhere.

Does anyone has an idea on how to do this?

Thank you
Tobias
Tagged:

Best Answer

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Answer ✓
    I tried creating a index und using the sql-contains clause, but this didn't bring me anywhere.
    What have you tried so far in this respect?

    Probably you should ike into something like
    var your_string varchar2(1000)
    
    exec :your_string := replace('Springfield + Simpson','+', ' and ')
    
    select *
      from person
     where contains (first_name, :your_string) > 1
        or contains (last_name, :your_string) > 1
    union all
    select *
      from city
     where contains (name, :your_string) > 1
        or contains (description, :your_string) > 1
    /

Answers

  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited Sep 3, 2008 10:25AM
    Did you tried like that ?
    ...
    where (column1 like '%criteria1%' and column1 like '%criteria2%')
    or    (column2 like '%criteria1%' and column2 like '%criteria2%')
    or    (column3 like '%criteria1%' and column3 like '%criteria2%')
    or    (column4 like '%criteria1%' and column4 like '%criteria2%')
    Nicolas.
    Nicolas Gasparotto
  • huber
    huber Member Posts: 91
    Hi Nicolas,

    thanks for your fast answer. Unfortunately this doesn't solve my problem. The query I am looking for receives only one string and I don't know how many searchCriterias there are in this string. There can be only 2 like in my example or there can be 10 or even more.

    Greetings
    Tobias
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    You are receiving only one string, but multiple criteria ?
    Like Springfield + Simpson it could be :
    %Springfield%Simpson% or %Simpson%Springfield%
    ?

    Nicolas.
    Nicolas Gasparotto
  • huber
    huber Member Posts: 91
    Hi Nicolas,

    this is also a good idea, but it doesn't help me either. Maybe I didn't explain my problem clear enough.

    I will explain my problem by giving the solution. The real problem is that I don't know how to realize this solution :-)
    1. I put the 4 columns (CITY.DESCRIPTION, CITY.NAME, PERSON.FIRST and PERSON.LAST_NAME) virtually in one column. It would become a long blob which than contains information about the city and the person.
    2. Afterwards I create an index of that virtual column
    3. Now I could use the following query to get a result
    SELECT * FROM virtualTable WHERE contains(virtualColumn, 'Simpson or Springfield', 1) > 0

    Simpson or Springfield could be replaced by any other string. More information about this [can be found here|http://download-west.oracle.com/docs/cd/B19306_01/text.102/b14218/cqoper.htm#i996733]

    The big problem lies in step 2. It would cost way too much to create a virtualColumn before each query. That's why I was asking here for another solution. Or maybe someone knows how to create an Index over different columns in different tables.

    Greetings
    Tobias
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    An issue you could have by concataining columns you can get value which meet your criteria. Example :
    col1 = simp
    col2 = son
    col1||col2 = simpson
    You are looking for simpson, does it ok with the values above ?

    Anyway, for me, the issue is not how to build a solution.
    The issue is about the requirement. It seems too strange to look for a value accross different columns in same time, especially when those columns are very different (like description or last name).
    Don't you know what information you have to look for ?

    Nicolas.
    Nicolas Gasparotto
  • huber
    huber Member Posts: 91
    N. Gasparotto wrote:
    An issue you could have by concataining columns you can get value which meet your criteria. Example :
    col1 = simp
    col2 = son
    col1||col2 = simpson
    You are looking for simpson, does it ok with the values above ?
    No that wouldn't be ok.
    col1 = Springfield
    col2 = Simpsons
    col1||col2 = Springfield and Simpsons
    Something like that is what I am looking for.

    >
    Anyway, for me, the issue is not how to build a solution.
    The issue is about the requirement. It seems too strange to look for a value accross different columns in same time, especially when those columns are very different (like description or last name).
    Don't you know what information you have to look for ?

    Nicolas.
    I have information about several customers in my database. I do have a detailed search in my webapplication, where the user can search for specific columns. But I also need to implement a global search (one inputTextField), which is always on the screen (top right). And this search functioniality is supposed to search through different columns like first_name, last_name or description.

    Greetings
    Tobias
  • There's a database feature for that (something about Text, can't recall name at all). We used if for a newspaper content management for web publishing where searches like the one You mention were required.
    Cuauhtemoc Amox
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Answer ✓
    I tried creating a index und using the sql-contains clause, but this didn't bring me anywhere.
    What have you tried so far in this respect?

    Probably you should ike into something like
    var your_string varchar2(1000)
    
    exec :your_string := replace('Springfield + Simpson','+', ' and ')
    
    select *
      from person
     where contains (first_name, :your_string) > 1
        or contains (last_name, :your_string) > 1
    union all
    select *
      from city
     where contains (name, :your_string) > 1
        or contains (description, :your_string) > 1
    /
  • Dan Blum
    Dan Blum Member Posts: 75 Blue Ribbon
    I agree with am0x - you need to use Oracle Text here. This will allow you to create an index on all the string columns in your table concatenated together, and will allow you to search for any number of strings ORed or ANDed together.

    If you are using 10.2, you can see the relevant Text Application Developer's Guide here:

    http://download.oracle.com/docs/cd/B19306_01/text.102/b14217/toc.htm
    Dan Blum
  • huber
    huber Member Posts: 91
    Thank you to all of you for your quick help.

    Tobias
This discussion has been closed.