5 Replies Latest reply on Sep 22, 2006 12:27 AM by Newsgroup_User

    Using MS SQL Like Queries

    Level 7
      Hi im running a MS sql database behind my Cold fusion site.

      I have a question on like queries that someone here will probably be able to
      help me
      I have a query that uses the following code

      name like '#search#%'


      The word Danger Sign appears as a name in my database.

      When I do a search on Danger Signs no records are returned.

      However when i do a search on Danger Sign my records are returned.


      I also tried to use

      name like '%#search#%'

      which also returned no results on Danger Signs

      Is the fact that my search term is two words causing the problem?

      Thanks in advance


        • 1. Re: Using MS SQL Like Queries
          The ScareCrow Level 1
          If the 2 words "Danger Sign" are in the field and you search for "Danger Signs" then no it will not match it because it does not match.

          To be able to do something like you want to do you will need to look into "full text searching" for sql server.

          Ken
          • 2. Re: Using MS SQL Like Queries
            Dan Bracuk Level 5
            Where is the search variable coming from? If it's a url, and it wasn't encoded properly, the space might cause a problem. Turn on debugging and look at sql sent to the db.
            • 3. Re: Using MS SQL Like Queries
              Level 7
              The ScareCrow wrote:
              > If the 2 words "Danger Sign" are in the field and you search for "Danger Signs"
              > then no it will not match it because it does not match.

              ken, actually i think that should work. i just tried

              SELECT uniLanguage
              FROM unicodeTest
              WHERE uniLanguage LIKE 'Chinese (%'

              and it correctly returned 2 rows (Chinese (Traditional) & Chinese (Simplified)).

              maybe the search term is getting bunged up?

              > To be able to do something like you want to do you will need to look into
              > "full text searching" for sql server.

              that's good advice. using wild cards in LIKE where clauses makes sql server do a
              table scan instead of using an index (unless the wild card is at the end of the
              search term). ms's full text indexing wins awards for it's search. i normally
              use it instead of verity.
              • 4. Using MS SQL Like Queries
                The ScareCrow Level 1
                Paul,
                Either, I have misunderstood this or you have ?

                I read the post as he was trying to find the string (forget danger)

                "signs"

                In a string that only contains "sign"

                Thus, let's assume the string is

                "This Danger Sign is red"

                Then if you try a search for

                Like 'Danger Signs%'

                It will not find it because the word signs is not in the string.

                Ken
                • 5. Re: Using MS SQL Like Queries
                  Level 7
                  The ScareCrow wrote:
                  > Either, I have misssunderstood this or you have ?

                  yup, i have.