Searching db Fields

Results 1 to 2 of 2

Thread: Searching db Fields

  1. #1
    Join Date
    Dec 1969

    Default Searching db Fields

    How do I search MS Access db fields that have the content seperated by commas? Say I am searching a field for both "dog and cat" and the field for some record contains the following:<BR><BR>rs1 - horse, dog, pig, cat, mouse<BR>rs2 - horse, dog, pig, camel, mouse, cow<BR><BR>Any help would be greatly appreciated.<BR><BR>Billy<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Don't do it

    That&#039;s bad DB design. Find a site that talks about NORMALIZATION in RDBS databases to see why.<BR><BR>You *really* need a one-to-many table, instead of the comma delimited list.<BR><BR>If you insist on using a list, then you need to create it thus:<BR><BR>,dog,cat,horse,pig,<BR><BR>Not there are *no spaces* in there and there is a comma on each end as well as between elements.<BR><BR>Then you can do:<BR><BR>SELECT * FROM table WHERE yuckoField LIKE &#039;%,dog,%&#039;<BR><BR>Where, of course, you substitute in whatever thing you are looking for in place of "dog".<BR><BR>See why it works? The commas on each end force the finding of complete words. If you simply did:<BR><BR>SELECT * FROM table WHERE yuckoField LIKE &#039;%dog%&#039;<BR><BR>Then you&#039;d get hits on "dogfish", "doghouse" and "hot dog". With the commas carefully in place, that doesn&#039;t happen.<BR><BR>But, really, learn about one-to-many tables and get rid of lists in the DB!<BR><BR><BR>

Posting Permissions

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