SQLite database: Order by a column with diacritics

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Cairoo
Posts: 107
Joined: Wed Dec 05, 2012 5:54 pm

SQLite database: Order by a column with diacritics

Post by Cairoo » Sat Nov 12, 2022 7:55 pm

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?

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9580
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: SQLite database: Order by a column with diacritics

Post by dunbarx » Sat Nov 12, 2022 10:54 pm

I do not use databases.

But back in good ol' LiveCode, cant you just replace all diacritical characters with their "plain" cousins? Something like:

Code: Select all

replace "é" with "e" in yourText
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

SparkOut
Posts: 2839
Joined: Sun Sep 23, 2007 4:58 pm

Re: SQLite database: Order by a column with diacritics

Post by SparkOut » Sat Nov 12, 2022 11:10 pm

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.

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9580
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: SQLite database: Order by a column with diacritics

Post by dunbarx » Sun Nov 13, 2022 12:04 am

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

SparkOut
Posts: 2839
Joined: Sun Sep 23, 2007 4:58 pm

Re: SQLite database: Order by a column with diacritics

Post by SparkOut » Sun Nov 13, 2022 12:43 am

"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

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 9580
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: SQLite database: Order by a column with diacritics

Post by dunbarx » Sun Nov 13, 2022 5:13 am

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

Cairoo
Posts: 107
Joined: Wed Dec 05, 2012 5:54 pm

Re: SQLite database: Order by a column with diacritics

Post by Cairoo » Sun Nov 13, 2022 7:24 am

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.

Post Reply

Return to “Databases”