LiveCode Forums

Dear forum,

How would I tell SQLite to ignore diacritics in the column it orders by? E.g. the word "Café" should be considered equal to "Cafe". The diacritics are stored in the table, but when querying the table and ordering by the column containing the diacritics, I need the word "Café" to appear before the word "California" in the query results.

Can this be done at the database level?

I do not use databases.

But back in good ol' LiveCode, cant you just replace all diacritical characters with their "plain" cousins? Something like: You would need a table of all possible diacritical chars.

I suppose you can also scan text char by char, and if, say its ASCII value is above 127, substitute right there. But again, you would need a table of substitutes, or rather write a function that does all that for you.

Craig

Look up the COLLATE options you have, it *might* be as simple as ...WHERE .... COLLATE NOACCENTS

or you may need to set up the database with collation of the right locale, or...

Basically, I *think* you should be able to achieve your desired results at the database level, and the solution will likely involve COLLATE.

Sparkout.

So good to have people here with broader experience than I do.

Are all DB's similar enough that a "native word" like "COLLATE" can be assumed to be part of the available tools?

Craig

"All" DBs are not always that similar, but SQL is generally rather close in its different flavours, such that you could expect a British and an American person to understand most things, while making adjustments for realise/realize, colour/color, and so on. There will also be some broader dialectal differences so someone from New Orleans and an East End Londoner might have greater communication differences while nominally still speaking the same language.

SQLite is probably a simpler implementation with a few fewer features than many others.

I found something which might be helpful to the OP here
https://dba.stackexchange.com/questions ... 975#190975

So the question really comes down to "where is the best place to process these odd characters?"

I assumed that LC does the processing, and databases merely hold the data. That is what I really meant by assuming (insinuating?) that LC is the place to be.

Craig

Thank you for all your responses.

@Sparkpout, I looked up the collate options and unfortunately SQLite doesn't offer the collate option I need. The easiest workaround I've found is to add a column for storing the values in Unicode Normal Form D (NFD), and search and sort the table with that new column.