Re: Retrieving partial field values

[Stephen Moretti]

Ian Grant wrote:

>On Wed, 05 Jan 2005 12:22:18 +0000
>Stephen Moretti <stephen@stripped> 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