It is currently Sat Feb 22, 2020 8:13 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: SQLite case insensitive query with greek utf8 chars
PostPosted: Sat Nov 23, 2019 8:47 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 748
Location: Athens, Greece
I have a db with uft8 values in greek like:
Code:
ΓΙΑΝΝΗΣ
Γιάννης
Γιαννης
(it is the word John in greek) and I want to write a query to be able to find all these 3 instances. In other words, I have words in upper case, words in lower case, words with or without tonos (= the mark ' on letter α, second row in my example). I tried adding " COLLATE NOCASE" at the end of the query and didn't help. Tried:
Code:
SELECT * FROM Table WHERE UPPER(item) LIKE 'UPPER(%text%')
and now I can find ΓΙΑΝΝΗΣ when searching for "γιαννησ". The last letter "ς" is handled as different than "Σ" as the lower case of "Σ" is "σ" (when a word ends with "σ", we use "ς" instead). What can I do to deal with mess?


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite case insensitive query with greek utf8 chars
PostPosted: Mon Nov 25, 2019 7:20 am 
Offline
Addict
Addict

Joined: Sat Feb 08, 2014 3:26 pm
Posts: 803
Quote:
and now I can find ΓΙΑΝΝΗΣ when searching for "γιαννησ". The last letter "ς" is handled as different than "Σ" as the lower case of "Σ" is "σ" (when a word ends with "σ", we use "ς" instead). What can I do to deal with mess?

First of all, if they are names, do not put a wildcard (%) at the beginning.
use text%

Then, to solve the problem, use the phonetics functions (Soundex)
They are different according to the databases and languages.
From memory there were also some PB tests. You should find examples in the forum.

_________________
(English is not my native language, I use an online translator)
Windows 10 Family x64 + Linux (Slackware, Debian on Oracle VirtualBox 6.0) + Raspberry Pi
All codes are tested with the latest version of PB, including beta versions at date of post.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite case insensitive query with greek utf8 chars
PostPosted: Mon Nov 25, 2019 2:53 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 748
Location: Athens, Greece
Marc56us wrote:
Quote:
and now I can find ΓΙΑΝΝΗΣ when searching for "γιαννησ". The last letter "ς" is handled as different than "Σ" as the lower case of "Σ" is "σ" (when a word ends with "σ", we use "ς" instead). What can I do to deal with mess?

First of all, if they are names, do not put a wildcard (%) at the beginning.
use text%

Then, to solve the problem, use the phonetics functions (Soundex)
They are different according to the databases and languages.
From memory there were also some PB tests. You should find examples in the forum.
I use % at the beginning because the full name may begin with the first name or the last name. I checked for soundex but no demo code showed up. What keywords should I use?


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite case insensitive query with greek utf8 chars
PostPosted: Mon Nov 25, 2019 4:28 pm 
Offline
Addict
Addict

Joined: Sat Feb 08, 2014 3:26 pm
Posts: 803
doctorized wrote:
I checked for soundex but no demo code showed up.
What keywords should I use?
Purebasic soundex

Sample
https://www.rosettacode.org/wiki/Soundex#PureBasic

The alternative method is to add a phonetic field to the database.

_________________
(English is not my native language, I use an online translator)
Windows 10 Family x64 + Linux (Slackware, Debian on Oracle VirtualBox 6.0) + Raspberry Pi
All codes are tested with the latest version of PB, including beta versions at date of post.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite case insensitive query with greek utf8 chars
PostPosted: Tue Nov 26, 2019 9:19 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Mar 27, 2009 9:41 am
Posts: 748
Location: Athens, Greece
Marc56us wrote:
doctorized wrote:
I checked for soundex but no demo code showed up.
What keywords should I use?
Purebasic soundex

Sample
https://www.rosettacode.org/wiki/Soundex#PureBasic

The alternative method is to add a phonetic field to the database.

I think that it is not what I need. I need some sort of collation but I do not know how to create one. I have found the way to call sqlite3_create_collation but don't know how to use it.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye