ASCII lookup table

[Rob Nicholson]

Not really VB related but I'll try here first :-) Looking for a lookup table
to match non-ASCII characters to their ASCII equivalent, if possible. For
example, the "o with an umlaut" on top would be matched to "o". It's part of
a search mechanism whereby if a user searches on bjorn it'll find the one
with the accents as well.

Cheers, Rob.

[Rob Nicholson]

> to match non-ASCII characters to their ASCII equivalent, if possible. For

example, the "o with an umlaut" on top would be matched to "o". It's part

of

I didn't explain myself very well :-) But the Unicode tables supplied are
just what we were looking for. There is an age old problem with searching
databases that contain Unicode text with accented characters like Björn.
Carry out a standard search like this:

Select * From People Where Firstname='Björ n'

And it'll work. However, carry out a search like this:

Select * From People Where Firstname='Bjor n'

And it won't return the same records AFAIK (unless something has changed).
So our solution is store two versions of every text field (OTT but database
isn't that big) so there's FirstName='Björ n' but this is also run through a
mangle/lookup to store ASCII_FirstName ='bjorn'. The search is also manged
and then the search carried out on the ASCII field.

If there is a SQL Server 2000 solution to this that works better, then I'd
love to hear!

Rob.

[Rob Nicholson]

> Select * From People Where Firstname='Björ n'

PS. And I'm kind of surprised that this came back through okay :-) Half
expected Usenet to 7 bit it all...

Rob.

[Peter Huang [MSFT]]

Hi Rob,

I think we need to do the map ourselves.
Refer to the links Herfried posted, we can build such lookup table
ourselves.
And when make query, we may try to use a OR operation.
e.g.
select * from table1 where name='A' OR name='A with accent'

NOTE: the relation between A and A with accent need to get from the lookup
table our built before.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

[jarl]

Rob Nicholson wrote:

to match non-ASCII characters to their ASCII equivalent, if possible. For example, the "o with an umlaut" on top would be matched to "o".

It's part of

I didn't explain myself very well :-) But the Unicode tables supplied are just what we were looking for. There is an age old problem with searching databases that contain Unicode text with accented characters like Björn. Carry out a standard search like this:

Select * From People Where Firstname='Björ n'

And it'll work. However, carry out a search like this:

Select * From People Where Firstname='Bjor n'

And it won't return the same records AFAIK (unless something has changed). So our solution is store two versions of every text field (OTT but database isn't that big) so there's FirstName='Björ n' but this is also run through a mangle/lookup to store ASCII_FirstName ='bjorn'. The search is also manged and then the search carried out on the ASCII field.

If there is a SQL Server 2000 solution to this that works better, then I'd love to hear!

Rob.

What you really want to do is to use an English accent insensitive
collation:

Select * From People Where Firstname COLLATE ENGLISH_CI_AI = 'Björn'

The above equals comparison will accept Bjorn, Björn, BJÖRN, BjôRn
etc.
HTH,
Jarl

[Rob Nicholson]

>Select * From People Where Firstname COLLATE ENGLISH_CI_AI = 'Björn'

The above equals comparison will accept Bjorn, Björn, BJÖRN, BjôRn etc.

Now you see, this is *just* the kind of thing that t'internet is
indepensible for! I'm aware of the collate mechanism but primarily from
problems with migrating databases from SQL 7 to SQL 2000 where the temporary
table collate is different to the old SQL 7 database. Nightmare!

AFAIR, isn't COLLATE a SQL 2000 new feature?

Cheers, Rob

[Peter Huang [MSFT]]

Hi

I am sorry I am not familar with SQL Server very much.
Based on my discussion with a SQL team engineer, the COLLATE is also
supported in SQL server 7.

If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.