SQLite database: Order by a column with diacritics
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
SQLite database: Order by a column with diacritics
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?
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?
-
- VIP Livecode Opensource Backer
- Posts: 9732
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: SQLite database: Order by a column with diacritics
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
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
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
Re: SQLite database: Order by a column with diacritics
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.
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.
-
- VIP Livecode Opensource Backer
- Posts: 9732
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: SQLite database: Order by a column with diacritics
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
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
Re: SQLite database: Order by a column with diacritics
"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
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
-
- VIP Livecode Opensource Backer
- Posts: 9732
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: SQLite database: Order by a column with diacritics
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
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
Re: SQLite database: Order by a column with diacritics
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.
@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.