Board index » delphi » accent and case sensitive query problem

accent and case sensitive query problem

An other problem in my queries....

In some cases, I have to insert in the Database some text entered by an
user...and later must retrieve this text by a query....

The problem is that the data can be either in lowercase than in
uppercase....and they can be accent in the text...(text is written in
French).

How can I find text in a table avoiding case sensitivity problems....

for example, the text can be:

genve (genéve) if you don't see the accent and know accents in HTML
Genve
genve
geneve.

I must find other records if I enter any of the typed names (if I put
accents, I must find records even if isn't written with accents...)

if the user type genve with an accent, my query must return all others type
writing....and also if I put geneve.

thanks

 

Re:accent and case sensitive query problem


Try using soundex algorithm to identify entries that sound similar but have
different writting.
you can import soundex function as a udf (have a glance at freeudflib.dll by
gregory daetz, you'll find it on interbase concerned sites) and use it in
your queries then. It converts a given string to a numerical system where
equal sounding letters are represented by equal numbers. accents don't make
a difference at all, so this might be exactly what your looking for.

if a zipcode or similar unique identifier is assigned to your text then you
could query an extra table and get valid text entries from that table and
let your user choose from a list. i've don this in one of my apps where the
user enters either a streetname and is presented valid zipcode/city
information or enters a zipcode and then selects a cityname from a list.

to keep it small you can simple query the data already entered in your
database assuming it has been entered correctly the first time.

regards, clemens

"Bob Bedford" <bedfo...@NOSPAMhotmail.com> schrieb im Newsbeitrag
news:3c020c53$1_2@dnews...

Quote
> An other problem in my queries....

> In some cases, I have to insert in the Database some text entered by an
> user...and later must retrieve this text by a query....

> The problem is that the data can be either in lowercase than in
> uppercase....and they can be accent in the text...(text is written in
> French).

> How can I find text in a table avoiding case sensitivity problems....

> for example, the text can be:

> genve (gen&eacute;ve) if you don't see the accent and know accents in
HTML
> Genve
> genve
> geneve.

> I must find other records if I enter any of the typed names (if I put
> accents, I must find records even if isn't written with accents...)

> if the user type genve with an accent, my query must return all others
type
> writing....and also if I put geneve.

> thanks

Re:accent and case sensitive query problem


By the way, soundex is case insensitive.
Genve would look something like G5100 as well as genve, geneve, genefe,
genf etc.

"CSS" <c...@main-rheiner.de> schrieb im Newsbeitrag
news:3c023d44$1_1@dnews...

Quote
> Try using soundex algorithm to identify entries that sound similar but
have
> different writting.
> you can import soundex function as a udf (have a glance at freeudflib.dll
by
> gregory daetz, you'll find it on interbase concerned sites) and use it in
> your queries then. It converts a given string to a numerical system where
> equal sounding letters are represented by equal numbers. accents don't
make
> a difference at all, so this might be exactly what your looking for.

> if a zipcode or similar unique identifier is assigned to your text then
you
> could query an extra table and get valid text entries from that table and
> let your user choose from a list. i've don this in one of my apps where
the
> user enters either a streetname and is presented valid zipcode/city
> information or enters a zipcode and then selects a cityname from a list.

> to keep it small you can simple query the data already entered in your
> database assuming it has been entered correctly the first time.

> regards, clemens

> "Bob Bedford" <bedfo...@NOSPAMhotmail.com> schrieb im Newsbeitrag
> news:3c020c53$1_2@dnews...
> > An other problem in my queries....

> > In some cases, I have to insert in the Database some text entered by an
> > user...and later must retrieve this text by a query....

> > The problem is that the data can be either in lowercase than in
> > uppercase....and they can be accent in the text...(text is written in
> > French).

> > How can I find text in a table avoiding case sensitivity problems....

> > for example, the text can be:

> > genve (gen&eacute;ve) if you don't see the accent and know accents in
> HTML
> > Genve
> > genve
> > geneve.

> > I must find other records if I enter any of the typed names (if I put
> > accents, I must find records even if isn't written with accents...)

> > if the user type genve with an accent, my query must return all others
> type
> > writing....and also if I put geneve.

> > thanks

Re:accent and case sensitive query problem


this example, even if interesting, doesn't fit to my example:
The problem is that people using my program know the exact spelling of the
cities names, but sometimes they will write with or without accents: that's
my problem. my query must return a record even if it's written without
accents and the user uses accents....that's nothing to do with mispelling
because the user doesn't know how to type a city name.

Thanks anyway for your help

"CSS" <c...@main-rheiner.de> a crit dans le message news:
3c023e64_1@dnews...

Quote
> By the way, soundex is case insensitive.
> Genve would look something like G5100 as well as genve, geneve, genefe,
> genf etc.

> "CSS" <c...@main-rheiner.de> schrieb im Newsbeitrag
> news:3c023d44$1_1@dnews...
> > Try using soundex algorithm to identify entries that sound similar but
> have
> > different writting.
> > you can import soundex function as a udf (have a glance at
freeudflib.dll
> by
> > gregory daetz, you'll find it on interbase concerned sites) and use it
in
> > your queries then. It converts a given string to a numerical system
where
> > equal sounding letters are represented by equal numbers. accents don't
> make
> > a difference at all, so this might be exactly what your looking for.

> > if a zipcode or similar unique identifier is assigned to your text then
> you
> > could query an extra table and get valid text entries from that table
and
> > let your user choose from a list. i've don this in one of my apps where
> the
> > user enters either a streetname and is presented valid zipcode/city
> > information or enters a zipcode and then selects a cityname from a list.

> > to keep it small you can simple query the data already entered in your
> > database assuming it has been entered correctly the first time.

> > regards, clemens

> > "Bob Bedford" <bedfo...@NOSPAMhotmail.com> schrieb im Newsbeitrag
> > news:3c020c53$1_2@dnews...
> > > An other problem in my queries....

> > > In some cases, I have to insert in the Database some text entered by
an
> > > user...and later must retrieve this text by a query....

> > > The problem is that the data can be either in lowercase than in
> > > uppercase....and they can be accent in the text...(text is written in
> > > French).

> > > How can I find text in a table avoiding case sensitivity problems....

> > > for example, the text can be:

> > > genve (gen&eacute;ve) if you don't see the accent and know accents in
> > HTML
> > > Genve
> > > genve
> > > geneve.

> > > I must find other records if I enter any of the typed names (if I put
> > > accents, I must find records even if isn't written with accents...)

> > > if the user type genve with an accent, my query must return all
others
> > type
> > > writing....and also if I put geneve.

> > > thanks

Re:accent and case sensitive query problem


then, maybe you can create your own procedure which compared only the
consonants in a given string and replaces all vocals by joker-symbols,
comparable to SQL-LIKE

regards, clemens

"Bob Bedford" <bedfo...@NOSPAMhotmail.com> schrieb im Newsbeitrag
news:3c026382$1_2@dnews...

Quote
> this example, even if interesting, doesn't fit to my example:
> The problem is that people using my program know the exact spelling of the
> cities names, but sometimes they will write with or without accents:
that's
> my problem. my query must return a record even if it's written without
> accents and the user uses accents....that's nothing to do with mispelling
> because the user doesn't know how to type a city name.

> Thanks anyway for your help

> "CSS" <c...@main-rheiner.de> a crit dans le message news:
> 3c023e64_1@dnews...
> > By the way, soundex is case insensitive.
> > Genve would look something like G5100 as well as genve, geneve,
genefe,
> > genf etc.

> > "CSS" <c...@main-rheiner.de> schrieb im Newsbeitrag
> > news:3c023d44$1_1@dnews...
> > > Try using soundex algorithm to identify entries that sound similar but
> > have
> > > different writting.
> > > you can import soundex function as a udf (have a glance at
> freeudflib.dll
> > by
> > > gregory daetz, you'll find it on interbase concerned sites) and use it
> in
> > > your queries then. It converts a given string to a numerical system
> where
> > > equal sounding letters are represented by equal numbers. accents don't
> > make
> > > a difference at all, so this might be exactly what your looking for.

> > > if a zipcode or similar unique identifier is assigned to your text
then
> > you
> > > could query an extra table and get valid text entries from that table
> and
> > > let your user choose from a list. i've don this in one of my apps
where
> > the
> > > user enters either a streetname and is presented valid zipcode/city
> > > information or enters a zipcode and then selects a cityname from a
list.

> > > to keep it small you can simple query the data already entered in your
> > > database assuming it has been entered correctly the first time.

> > > regards, clemens

> > > "Bob Bedford" <bedfo...@NOSPAMhotmail.com> schrieb im Newsbeitrag
> > > news:3c020c53$1_2@dnews...
> > > > An other problem in my queries....

> > > > In some cases, I have to insert in the Database some text entered by
> an
> > > > user...and later must retrieve this text by a query....

> > > > The problem is that the data can be either in lowercase than in
> > > > uppercase....and they can be accent in the text...(text is written
in
> > > > French).

> > > > How can I find text in a table avoiding case sensitivity
problems....

> > > > for example, the text can be:

> > > > genve (gen&eacute;ve) if you don't see the accent and know accents
in
> > > HTML
> > > > Genve
> > > > genve
> > > > geneve.

> > > > I must find other records if I enter any of the typed names (if I
put
> > > > accents, I must find records even if isn't written with accents...)

> > > > if the user type genve with an accent, my query must return all
> others
> > > type
> > > > writing....and also if I put geneve.

> > > > thanks

Re:accent and case sensitive query problem


Quote
Bob Bedford wrote:

> this example, even if interesting, doesn't fit to my example:
> The problem is that people using my program know the exact spelling of the
> cities names, but sometimes they will write with or without accents: that's
> my problem. my query must return a record even if it's written without
> accents and the user uses accents....that's nothing to do with mispelling
> because the user doesn't know how to type a city name.

        In that case use a shadow column with the un-accented name and search
on that.  You can write a simple UDF to strip the accents and update the
shadow column in a trigger.

        HTH,

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

Other Threads