Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Substring queries?

Expand Messages
  • (DMR) Mark Gorelik
    use : first_name like %JEF% Mark Gorelik Fujitsu Consulting 416-969-7141 ... From: jephmat@earthlink.net [mailto:jephmat@earthlink.net] Sent: Tuesday, July
    Message 1 of 9 , Jul 7, 2004
    • 0 Attachment
      use : first_name like '%JEF%'

      Mark Gorelik
      Fujitsu Consulting
      416-969-7141



      -----Original Message-----
      From: jephmat@... [mailto:jephmat@...]
      Sent: Tuesday, July 06, 2004 10:08 PM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: [SQLQueriesNoCode] Substring queries?


      Hello there! My first post.

      I am working on a database that uses first and last names in its
      search parameters. I am using traditional queries such as SELECT *
      FROM TABLE WHERE FIRST_NAME = 'JEFFREY';

      But is there a way you can fashion the SQL query itself so that it
      looks for a substring? For example, is there a way to have the query
      look up the string 'JEFF' and also include in its results the
      names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
      with J-E-F-F?






      Yahoo! Groups Links






      *** This communication is strictly private and contains confidential information intended solely for the addressee(s). Access to this communication by parties other than the intended recipient(s) is unauthorized. If you have received this communication in error and are not the intended recipient, any disclosure, copying, distribution or action taken is strictly prohibited and may be unlawful. ***

      *** Ce message est strictement prive et contient des renseignements confidentiels destines uniquement au(x) destinataire(s). L'acces a ce message par des parties autres que le(s) destinataire(s) prevu(s) n'est pas autorise. Si vous avez re├žu ce message par erreur et n'etes pas le destinataire prevu, toute divulgation, copie, distribution ou action prise est strictement interdite et peut etre consideree comme illegale. ***
    • Joe Delpha
      Have a go with SELECT * FROM TABLE WHERE FIRST_NAME LIKE JEFF% Note that the syntax may vary from vendor to vendor. ... From: jephmat@earthlink.net
      Message 2 of 9 , Jul 7, 2004
      • 0 Attachment
        Have a go with SELECT * FROM TABLE WHERE FIRST_NAME LIKE 'JEFF%'
        Note that the syntax may vary from vendor to vendor.

        -----Original Message-----
        From: jephmat@... [mailto:jephmat@...]
        Sent: Tuesday, July 06, 2004 10:08 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Substring queries?

        Hello there! My first post.

        I am working on a database that uses first and last names in its
        search parameters. I am using traditional queries such as SELECT *
        FROM TABLE WHERE FIRST_NAME = 'JEFFREY';

        But is there a way you can fashion the SQL query itself so that it
        looks for a substring? For example, is there a way to have the query
        look up the string 'JEFF' and also include in its results the
        names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
        with J-E-F-F?






        Yahoo! Groups Links
      • John Warner
        SELECT First_Name FROM Table WHERE First_Name Like Jeff% ; John Warner mailto:john@jwarner.com
        Message 3 of 9 , Jul 7, 2004
        • 0 Attachment
          SELECT First_Name
          FROM Table
          WHERE First_Name Like 'Jeff%';

          John Warner
          mailto:john@...

          > -----Original Message-----
          > From: jephmat@... [mailto:jephmat@...]
          > Sent: Tuesday, July 06, 2004 10:08 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] Substring queries?
          >
          >
          > Hello there! My first post.
          >
          > I am working on a database that uses first and last names in its
          > search parameters. I am using traditional queries such as SELECT *
          > FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
          >
          > But is there a way you can fashion the SQL query itself so that it
          > looks for a substring? For example, is there a way to have the query
          > look up the string 'JEFF' and also include in its results the
          > names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
          > with J-E-F-F?
          >
          >
          >
          >
          > ------------------------ Yahoo! Groups Sponsor
          > --------------------~-->
          > Yahoo! Domains - Claim yours for only $14.70
          > http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/m7folB/T> M
          >
          >
          > --------------------------------------------------------------
          > ------~->
          >
          >
          > Yahoo! Groups Links
          >
          >
          >
          >
          >
          >
        • Michael Gerholdt
          ... this is quite simple because you need not actually treat it as a substr and use any functions to work on the data. Instead, just use the SQL operator
          Message 4 of 9 , Jul 7, 2004
          • 0 Attachment
            >
            > I am working on a database that uses first and last names in its
            > search parameters. I am using traditional queries such as SELECT *
            > FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
            >
            > But is there a way you can fashion the SQL query itself so that it
            > looks for a substring? For example, is there a way to have the query
            > look up the string 'JEFF' and also include in its results the
            > names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
            > with J-E-F-F?
            >
            >

            this is quite simple because you need not actually treat it as a substr and
            use any functions to work on the data. Instead, just use the SQL operator
            "LIKE" instead of "=" and then make sure you use the percent sign as a wild
            card (unless you are in Access where I think it is an asterisk)

            Do a search on "LIKE" operator in SQL and you'll be set


            where first_name like 'JEFF%'

            does what you want above

            where first_name like '%na'

            will get 'Anna' and 'banana' and anything that ends in 'na'

            where first name like 'J%y'

            Joy, Jimmy, Jeffrey, etc

            where first_name like 'J_y'

            Joy, Jay
            (the underscore limits to one character)
          • George Clay
            There are a couple of ways to do this, using CharIndex or Like. Select * From Table Where FirstName Like Jeff% Select * From Table Where CharIndex( Jeff ,
            Message 5 of 9 , Jul 7, 2004
            • 0 Attachment
              There are a couple of ways to do this, using CharIndex or Like.

              Select * From Table Where FirstName Like 'Jeff%'

              Select * From Table Where CharIndex('Jeff', FirstName) > 0


              George Clay
              SQL Server DBA
              www.sqldudes.com

              --- jephmat@... wrote:
              > Hello there! My first post.
              >
              > I am working on a database that uses first and last names in its
              > search parameters. I am using traditional queries such as SELECT *
              > FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
              >
              > But is there a way you can fashion the SQL query itself so that it
              > looks for a substring? For example, is there a way to have the query
              > look up the string 'JEFF' and also include in its results the
              > names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
              > with J-E-F-F?
              >
              >
              >
              >
              >
              >
              > Yahoo! Groups Links
              >
              >
              >
              >
              >
              >




              __________________________________
              Do you Yahoo!?
              Yahoo! Mail is new and improved - Check it out!
              http://promotions.yahoo.com/new_mail
            • Razvan Socol
              SELECT * FROM your_table WHERE first_name LIKE JEFF% If you use Microsoft SQL Server, you may also want to try: SELECT * FROM your_table WHERE
              Message 6 of 9 , Jul 7, 2004
              • 0 Attachment
                SELECT * FROM your_table WHERE first_name LIKE 'JEFF%'

                If you use Microsoft SQL Server, you may also want to try:

                SELECT * FROM your_table WHERE DIFFERENCE(first_name, 'Jeffrey')>2

                This query can find even variations like "Geoffrey", "Jefry", etc.

                For more informations, see "Comparing SOUNDEX and DIFFERENCE" in BOL:
                http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_11_1rj9.asp

                Razvan

                ----- Original Message -----
                From: jephmat@...
                To: SQLQueriesNoCode@yahoogroups.com
                Sent: Wednesday, July 07, 2004 5:07 AM
                Subject: [SQLQueriesNoCode] Substring queries?

                I am working on a database that uses first and last names in its
                search parameters. I am using traditional queries such as SELECT *
                FROM TABLE WHERE FIRST_NAME = 'JEFFREY';

                But is there a way you can fashion the SQL query itself so that it
                looks for a substring? For example, is there a way to have the query
                look up the string 'JEFF' and also include in its results the
                names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
                with J-E-F-F?
              • Iian Neill
                Hi, For a farther-ranging and perhaps more intuitive search you might like to use SQL Server s built-in SOUNDEX command. The process dates back to 19th
                Message 7 of 9 , Jul 7, 2004
                • 0 Attachment
                  Hi,

                  For a farther-ranging and perhaps more intuitive search you might like to
                  use SQL Server's built-in SOUNDEX command. The process dates back to 19th
                  century, when census-takers needed to account for accidental or traditional
                  variations in the spellings of surnames: for example, "MacFadyen" and
                  "McFadden". To my understanding, both names are valid spellings of the same
                  name, but would not be treated as such in the typical alphabetical listing.
                  The SOUNDEX process compares the consonantal components - in which case,
                  both names might render something like "MCFDN".

                  Apparently the command can also rank the similarity of the matches by
                  percent. I don't think it works (without tweaking) for surnames that
                  include hyphens, apostrophes, spaces (i.e, Leonardo "da Vinci"), etc. You
                  might need to process your search string first (by removing the illegal
                  characters).

                  regards,
                  Iian

                  At 12:45 PM 7/7/2004 -0700, you wrote:
                  >There are a couple of ways to do this, using CharIndex or Like.
                  >
                  >Select * From Table Where FirstName Like 'Jeff%'
                  >
                  >Select * From Table Where CharIndex('Jeff', FirstName) > 0
                  >
                  >
                  >George Clay
                  >SQL Server DBA
                  >www.sqldudes.com
                  >
                  >--- jephmat@... wrote:
                  > > Hello there! My first post.
                  > >
                  > > I am working on a database that uses first and last names in its
                  > > search parameters. I am using traditional queries such as SELECT *
                  > > FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
                  > >
                  > > But is there a way you can fashion the SQL query itself so that it
                  > > looks for a substring? For example, is there a way to have the query
                  > > look up the string 'JEFF' and also include in its results the
                  > > names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
                  > > with J-E-F-F?
                  > >
                  > >
                  > >
                  > >
                  > >
                  > >
                  > > Yahoo! Groups Links
                  > >
                  > >
                  > >
                  > >
                  > >
                  > >
                  >
                  >
                  >
                  >
                  >__________________________________
                  >Do you Yahoo!?
                  >Yahoo! Mail is new and improved - Check it out!
                  >http://promotions.yahoo.com/new_mail
                  >
                  >
                  >
                  >
                  >
                  >Yahoo! Groups Links
                  >
                  >
                  >
                  >
                • James P
                  yes..you can use the soundex function for this...see the books online for the detailed syntax ... From: SQLQueriesNoCode@yahoogroups.com Date: 07/08/04
                  Message 8 of 9 , Jul 7, 2004
                  • 0 Attachment
                    yes..you can use the soundex function for this...see the books online for the detailed syntax 
                     
                    -------Original Message-------
                     
                    Date: 07/08/04 04:27:18
                    Subject: Re: [SQLQueriesNoCode] Substring queries?
                     
                    SELECT * FROM your_table WHERE first_name LIKE 'JEFF%'
                     
                    If you use Microsoft SQL Server, you may also want to try:
                     
                    SELECT * FROM your_table WHERE DIFFERENCE(first_name, 'Jeffrey')>2
                     
                    This query can find even variations like "Geoffrey", "Jefry", etc.
                     
                    For more informations, see "Comparing SOUNDEX and DIFFERENCE" in BOL:
                     
                    Razvan
                     
                    ----- Original Message -----
                    Sent: Wednesday, July 07, 2004 5:07 AM
                    Subject: [SQLQueriesNoCode] Substring queries?
                     
                    I am working on a database that uses first and last names in its
                    search parameters.  I am using traditional queries such as SELECT *
                    FROM TABLE WHERE FIRST_NAME = 'JEFFREY';
                     
                    But is there a way you can fashion the SQL query itself so that it
                    looks for a substring?  For example, is there a way to have the query
                    look up the string 'JEFF' and also include in its results the
                    names 'JEFFREY', 'JEFFERY', 'JEFFY' and anything else that begins
                    with J-E-F-F?
                     
                     
                     
                     
                    ------------------------ Yahoo! Groups Sponsor --------------------~-->
                    Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar.
                    Now with Pop-Up Blocker. Get it for free!
                    --------------------------------------------------------------------~->
                     
                     
                    Yahoo! Groups Links
                     
                    <*> To visit your group on the web, go to:
                     
                    <*> To unsubscribe from this group, send an email to:
                     
                    <*> Your use of Yahoo! Groups is subject to:
                     
                     
                    ____________________________________________________
                      IncrediMail - Email has finally evolved - Click Here
                  Your message has been successfully submitted and would be delivered to recipients shortly.