Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: SQL pattern matching

    Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:

    ID NAME KEYWORDS
    --- ------- -----------
    AA025 NAME1 attached, atic, common, business, hotels
    AA026 NAME2 headache, medicin, aspirins, heat, health
    AA027 NAME3 at, services, music, electronics

    suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID.
    Thanks in advance guys.
    Cheers.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why the last one and not the others? What is the rule you want to use?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM   myTable
    WHERE  id = (
                 SELECT Max(id)
                 FROM   myTable
                 WHERE  someField LIKE '%ab%'
                )
    Here's a generic stab at the answer.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm guessing ou want complete words only.

    Most sensible thing would be to normalise your data. Otherwise easiest method, if you won't or can't, is:
    Code:
     KEYWORDS LIKE 'at,%' OR KEYWORDS LIKE '%, at,% OR KEYWORDS LIKE '%, at'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Code:
    SELECT *
    FROM   myTable
    WHERE  id = (
                 SELECT Max(id)
                 FROM   myTable
                 WHERE  someField LIKE '%ab%'
                )
    Here's a generic stab at the answer.
    I hope that's NZDF....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Would you expect any less from me?


    Actually, don't answer that!
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2008
    Posts
    4
    Those were such a quick answers!, thanks. Your assumption is right pootle_flump, i want just complete words to match. What do you mean with normalize my data? I'm pretty new in database's tasks.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Normalisation is a fundamental part of relational database design. This looks like a big article but it is actually just a run through the basic concepts. Read through it - I can assure you that it will reduce your overall development time. Time spent up front designing your database is recrued later as your SQL is typically much easier to write.

    http://www.tonymarston.net/php-mysql...se-design.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2008
    Posts
    4
    I jump to data normalization section, but the whole article seems to be really interesting. Wich way of both you said above would be more efficient?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    1 vote for normalised.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Make that 2 votes, especially since poots spelled normalised correctly
    George
    Home | Blog

  12. #12
    Join Date
    Sep 2005
    Posts
    161
    You're asking a forum where database developers hang out. You won't find many votes against normalisation.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ain't that the truth And four correct spellings of normalisation in a row - this thread is gold.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2008
    Posts
    4
    jaja sorry guys for my spelling. I speak spanish, that's my language. We have the same word, but in our language we write normalization. I wrote it automatically, without thinking. Thanks for your replies.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mariano_donati
    jaja sorry guys for my spelling. I speak spanish, that's my language. We have the same word, but in our language we write normalization. I wrote it automatically, without thinking. Thanks for your replies.
    Oh gosh no - we weren't getting at you. Just goading the Americans for changing the spelling of all our treasdured words (like colour *color* and grey *gray* and organised *organized*). You should try goading Americans - it's great fun. Watch - one will come along shortly and strike back.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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