Like and Soundex help please

Hello, I wonder if anyone can help me
My Table
ID Sunames Inscription
typical entrys
ID 10
Surnames @ABBOTT@JONES@
rem the @ is there so that I can select a whole word and not get ABBOT
Inscription John ABBOTT died 10 june 1857 also his sister mary JONES died 16
may 1868

my query
if ($soundex==exact)
$query="Select * FROM paupers WHERE surnames LIKE '%@$name@%'"
else
$query="Select * FROM paupers WHERE soundex(surnames) LIKE
'%@(soundex($name))@%'"

the first part -- LIKE '%@$name@%' works fine
but I can`t get the syntax for the soundex bit right
any help appreciated
Regards

Re: Like and Soundex help please

SqlDope wrote:
> Hello, I wonder if anyone can help me
> My Table
> ID Sunames Inscription
> typical entrys
> ID 10
> Surnames @ABBOTT@JONES@
> rem the @ is there so that I can select a whole word and not get ABBOT
> Inscription John ABBOTT died 10 june 1857 also his sister mary JONES
> died 16 may 1868
>
> my query
> if ($soundex==exact)
> $query="Select * FROM paupers WHERE surnames LIKE '%@$name@%'"
> else
> $query="Select * FROM paupers WHERE soundex(surnames) LIKE
> '%@(soundex($name))@%'"
>
> the first part -- LIKE '%@$name@%' works fine
> but I can`t get the syntax for the soundex bit right
> any help appreciated
> Regards

You should not put lots of surnames in a single record. Each surname should
occupy its own single field in a single record.

Re: Like and Soundex help please

Hi Paul,
I understand your point there but in this case I need to have up to 5
surnames in a string in surnames field, these are the surnames in that
particular inscription
other wise I would have to have 5 surname fields -- surname1 surname2 ....
and then do a seperate select on each surname field
Regards
"Paul Lautman" wrote in message
news:50hhroF1frfrvU1@mid.individual.net...
> SqlDope wrote:
>> Hello, I wonder if anyone can help me
>> My Table
>> ID Sunames Inscription
>> typical entrys
>> ID 10
>> Surnames @ABBOTT@JONES@
>> rem the @ is there so that I can select a whole word and not get ABBOT
>> Inscription John ABBOTT died 10 june 1857 also his sister mary JONES
>> died 16 may 1868
>>
>> my query
>> if ($soundex==exact)
>> $query="Select * FROM paupers WHERE surnames LIKE '%@$name@%'"
>> else
>> $query="Select * FROM paupers WHERE soundex(surnames) LIKE
>> '%@(soundex($name))@%'"
>>
>> the first part -- LIKE '%@$name@%' works fine
>> but I can`t get the syntax for the soundex bit right
>> any help appreciated
>> Regards
>
> You should not put lots of surnames in a single record. Each surname
> should occupy its own single field in a single record.
>

Re: Like and Soundex help please

SqlDope wrote:
> "Paul Lautman" wrote in message
> news:50hhroF1frfrvU1@mid.individual.net...
>> SqlDope wrote:
>>> Hello, I wonder if anyone can help me
>>> My Table
>>> ID Sunames Inscription
>>> typical entrys
>>> ID 10
>>> Surnames @ABBOTT@JONES@
>>> rem the @ is there so that I can select a whole word and not get
>>> ABBOT Inscription John ABBOTT died 10 june 1857 also his sister
>>> mary JONES died 16 may 1868
>>>
>>> my query
>>> if ($soundex==exact)
>>> $query="Select * FROM paupers WHERE surnames LIKE '%@$name@%'"
>>> else
>>> $query="Select * FROM paupers WHERE soundex(surnames) LIKE
>>> '%@(soundex($name))@%'"
>>>
>>> the first part -- LIKE '%@$name@%' works fine
>>> but I can`t get the syntax for the soundex bit right
>>> any help appreciated
>>> Regards
>>
>> You should not put lots of surnames in a single record. Each surname
>> should occupy its own single field in a single record.
> Hi Paul,
> I understand your point there but in this case I need to have up to 5
> surnames in a string in surnames field, these are the surnames in that
> particular inscription
> other wise I would have to have 5 surname fields -- surname1 surname2
> .... and then do a seperate select on each surname field
> Regards

Please do not top post!

No you DO NOT need to have 5 surnames in a single field. And I did NOT say
that you should have 5 surname fields. I very carefully said that you should
have each surname in a SINGLE field in a SINGLE record. You can then tie 5
or as many of those records as you like together with a common foreign key.

It is called database normalisation.

Re: Like and Soundex help please

"Paul Lautman" wrote in message
news:50htf7F1g3unvU1@mid.individual.net...
> SqlDope wrote:
>> "Paul Lautman" wrote in message
>> news:50hhroF1frfrvU1@mid.individual.net...
>>> SqlDope wrote:
>>>> Hello, I wonder if anyone can help me
>>>> My Table
>>>> ID Sunames Inscription
>>>> typical entrys
>>>> ID 10
>>>> Surnames @ABBOTT@JONES@
>>>> rem the @ is there so that I can select a whole word and not get
>>>> ABBOT Inscription John ABBOTT died 10 june 1857 also his sister
>>>> mary JONES died 16 may 1868
>>>>
>>>> my query
>>>> if ($soundex==exact)
>>>> $query="Select * FROM paupers WHERE surnames LIKE '%@$name@%'"
>>>> else
>>>> $query="Select * FROM paupers WHERE soundex(surnames) LIKE
>>>> '%@(soundex($name))@%'"
>>>>
>>>> the first part -- LIKE '%@$name@%' works fine
>>>> but I can`t get the syntax for the soundex bit right
>>>> any help appreciated
>>>> Regards
>>>
>>> You should not put lots of surnames in a single record. Each surname
>>> should occupy its own single field in a single record.
>> Hi Paul,
>> I understand your point there but in this case I need to have up to 5
>> surnames in a string in surnames field, these are the surnames in that
>> particular inscription
>> other wise I would have to have 5 surname fields -- surname1 surname2
>> .... and then do a seperate select on each surname field
>> Regards
>
> Please do not top post!
>
> No you DO NOT need to have 5 surnames in a single field. And I did NOT say
> that you should have 5 surname fields. I very carefully said that you
> should have each surname in a SINGLE field in a SINGLE record. You can
> then tie 5 or as many of those records as you like together with a common
> foreign key.
>
> It is called database normalisation.
>
Hi Paul, I took your advise and all is working fine now.
Thank you again and regards