Ian Grant wrote:
On Wed, 05 Jan 2005 12:22:18 +0000 Stephen Moretti <[EMAIL PROTECTED]> wrote:
Kentucky Families wrote:
... If I use a VARCHAR or TINYEXT field to enter these values and I want to be able to retrieve all records where the surname field contains the whole word Stotts, how would I enter these values:
stotts or statts or stutts
stotts,statts,stutts
other?
You need to do an IN query.
SELECT column,list,here
FROM tblBMD
WHERE Surname IN ('stotts','statts','stutts')
This will not match any of the records with multiple transcriptions. I think you have mis-understood the question. As I understand it she
asks how to enter multiple possible transcriptions into the field so
that they can be retrieved easily.
My answer is: since commas are unlikely in names, that is as good a separator as any. To do the query use wildcard matching with LIKE e.g.
SELECT * FROM table WHERE surname LIKE '%stotts%';
will match a field 'Stotts,Statts,Stutts' or just 'Stotts' but note it
will also match e.g. 'Stottsford,Stattsford,Stuttsford'
Ah see what you mean.
I really would strongly recommend against storing more than one surname in a field, no matter how you decide to delimit them.
It should be noted that BMDs are generally consider legal notices. All efforts should be made to ensure that the data given to the transcribers is clear and correct, so that the correct data is entered first time.
If you want to do a "similar" look up, then you will need a couple of look up tables that allow a user to look up a surname and get back a list of similar surnames (this would be transparent to the user), which can then be used to query the BMDs.
Stephen
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
