Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Aug 2005
    Posts
    12

    Unanswered: Search Procedure

    Dear friends,
    I trust that you will give me a solution.
    I have a table of item names like this:

    Small trolley spiderman bag
    Large trolley spiderman bag
    Small spiderman pencilcase
    Round white rubber
    Small metalic double holes sharpener

    I need a procedure or a function which allows the user to enter some parts of letters in any order and it returns the items which contain those letters.
    For example:
    When a user enters: "tr ma" the function returns the first item which contains the both words "trolley" and "Small"
    When a user enters: "sp" the function returns the three items which contain "spiderman"

    I hope that I could explain the issue
    Thanks in advance for your help.

    Haytham

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You just need to use the LIKE() function with % wildcards. Look it up in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2005
    Posts
    12

    Like is not useful

    Dear blindman;
    thanks for your quick reply.
    I can't use (like) operator because I don't know how many word parts and in which order the user will enter them. He may enter for example "tr sma" or "sm tro" or "tr sm sp" or "tr pi sm" or "tro"........ and he want's to see all the item names which contain these word parts.

    Haytham

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think it's the double matching thing he's interested in

    I'd split the values up, and load them into a table variable then join to it with a LIKE...is that a theta join?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haytham
    When a user enters: "tr ma" the function returns the first item which contains the both words "trolley" and "Small"
    Code:
    WHERE ' '+itemname LIKE '% tr%' 
      AND ' '+itemname LIKE '% ma%'
    Quote Originally Posted by Haytham
    When a user enters: "sp" the function returns the three items which contain "spiderman"
    Code:
    WHERE ' '+itemname LIKE '% sp%'
    and no, that's not a theta join

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Give this a shot and modify as needed:

    declare @Data varchar(8000)
    set @Data = 'AG lo wa th'

    while @Data <> ''
    begin

    if charindex(' ',@Data) <> 0
    begin
    select distinct mycolumn
    from mytable <-- change this to your table
    where mycolumn like '%' + substring(@Data,1,charindex(' ',@Data)-1) + '%'
    order by mycolumn

    set @Data = substring(@Data,charindex(' ',@Data) + 1,len(@Data))
    end
    else
    begin
    select distinct mycolumn
    from mytable <-- change this to your table
    where mycolumn like '%' + @Data + '%'
    order by mycolumn

    set @Data = ''
    end

    end
    You'll have to use your own columns and tables.

  7. #7
    Join Date
    Aug 2005
    Posts
    12

    Thaaaaaaaaanks

    Dear corncrowe,
    Thaaaaaaaaaaaaaaaaaanks for your helpful script.
    The script worked just for the first word. In the example which you had given, you wrote @Data = 'AG lo wa th' and when I had run the script it searched for just 'AG', but I want it to serch for the fields which contain 'AG' and 'lo' and 'wa' and 'th'.

    I will be waiting for your helpful reply.

    Thanks

  8. #8
    Join Date
    Nov 2005
    Posts
    122
    How many rows of data will this table have approximately?

    The reason I'm asking is because the methods suggested will case index or table scans which are bad performance wise.

    I suggests you take a look at the Full-Text index option that SQL Server have. It gives you much better freetext searches than you you can build using LIKE.

  9. #9
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Question input is given in a development

    Solve it in your development code
    , not in the SQL server

    you'l have to chop the input in to a X-number of pieces
    that isn't do'able in SQL

    can't you do a routine in your code like this
    when they press a ok button.

    Say : txtINPUT.text= "MA SP"

    dim iBegin as int =0
    dim iEnd as int =0

    mySQL = "SELECT * FROM ARTICLE WHERE" 'Start sql buildup

    DO until iBegin = len(txtINPUT.text)

    if instr(iBegin,txtINPUT.text," ") > 0 then iEnd = instr(txtINPUT.text," ")

    if iEnd>0 then
    mySQL= mySQL & " NAME LIKE %" & mid(txtINPUT,iBegin,iEnd) & "% OR " '2extra space
    iBegin = iEnd + 1
    iEnd=0
    else
    mySQL= mySQL & " NAME LIKE %" & mid(txtINPUT,iBegin) & "% OR " '2extra space
    iBegin = len(txtINPUT.text)
    endif
    LOOP

    'SQL will be "SELECT * FROM ARTICLE WHERE NAME LIKE %MA% OR NAME LIKE %SP% OR "
    mySQL=mid(mySQL ,1,len(mySQL)-5) 'SQL now is "SELECT * FROM ARTICLE WHERE NAME LIKE %MA% OR NAME LIKE %SP%"
    MyDataTable.Excecute(MySQL)

  10. #10
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by r937
    and no, that's not a theta join
    Why not? I thought anything not being a equi-join is a theta-join? Perhaps I'm mistaken.

  11. #11
    Join Date
    Aug 2005
    Posts
    12
    my items table contains about 10000 rows.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kaffenils
    Why not? I thought anything not being a equi-join is a theta-join? Perhaps I'm mistaken.
    no, i think i was mistaken

    i misunderstood what brett was suggesting, didn't see the table variable

    a join using LIKE is indeed a theta join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kaffenils
    Why not? I thought anything not being a equi-join is a theta-join? Perhaps I'm mistaken.
    Coo - turns out I was mistaken too. I thought a theta join was one that involved an inequality comparison rather than one that does not involve an equi-join. A subtle distinction

    EDIT - even that is not right. A theta join then is any that includes any operator except =, even if there is an = in the join too.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I agree with Marvels; handle this in your application code
    Code:
    Dim strSQL As String
    Dim inputStr As String
    Dim counter As Integer
    Dim items As Variant
    
        strSQL = "SELECT id, data FROM myTable WHERE 1=1"
    
        inputStr = Trim(Me.txtSearch.Value)
    
        items = Split(inputStr, " ")
    
        For counter = 0 To UBound(items)
            strSQL = strSQL & " AND data LIKE '*" & items(counter) & "*'"
        Next
    
        Debug.Print strSQL
    
        Me.resultsList.RowSource = strSQL
        Me.Refresh
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2005
    Posts
    122
    I still think Full-Text indexing is a better option as it gives more functionality, e.g. ranking.

    But if you still choose to use the LIKE-way and split parameters in the app code then please use paramerterized queries, or I will come and hack you system

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •