[thelist] multilingual sql databases and searching

[rudy]

> I wish to store large amounts of copy in a database, either MS SQL 2000
> or Oracle, that copy being in several different languages, e.g. French,
> Russian, Japanese. How can I do this in the above two databases?
> How do I setup the tables, what string types?

hi garry

large amounts are not a problem, you can have gazillions of rows and
neither of those databases will break a sweat

large individual chunks of copy might be a little trickier, although both
of them have the means for storing megabytes or even gigabytes in one row

for example, in oracle you can use VARCHAR2, which can go up to 4000, or
LONG, which can go up to 2gigs, or CLOB, up to 4gigs

sql/server has its own datatypes, such as CHAR and VARCHAR which can go up
to 8000 bytes, and TEXT, which can go up to 2 gigs

however, for multiple language support, the datatypes have to support
unicode, so in oracle you'll want to use the NCLOB datatype, and in
sql/server NVARCHAR or NTEXT

so a lot will depend on which database you're going to use and how much
copy you want to store in each "chunk"

>I would then like to do two kinds of searching on this data:
>(1) Case-insensitive: so I search on "food", and the search will find
>"FooD", fOOd", etc.

a lot will depend on the database

you will need to ensure that scalar functions such as LOWER will work on
the unicode datatype (i've never done this)

>(2) Diacritic-insensitive: so I search on "food", and the search will find
>all instances of data with "food" in regardless of accents or other such
>characters.

good question

i know sql/server has some server setting that governs this, so i believe
it's possible

dex?  anthony?  buehler?


>Finally - supposing I had 2-3 such databases, how could I search all 2-3
>simultaneously?

if you mean multiple tables in the same database, just use UNION

if you really mean separate databases, it would depend on how nice you've
been to your database administrator, as i believe it requires special
server side magic

rudy