2

I have two SQLite tables, that I would love to join them on a name column. This column contains accented characters, so I am wondering how can I compare them for join. I would like the accents dropped for the comparison to work.

Pentium10
  • 204,586
  • 122
  • 423
  • 502

4 Answers4

8

You can influence the comparison of characters (such as ignoring case, ignoring accents) by using a Collation. SQLLite has only a few built in collations, although you can add your own.

EDIT: Given that it seems doubtful if Android supports UDFs and computed columns, here's another approach:

  • Add another column to your table, normalizedName
  • When your app writes out rows to your table, it normalizes name itself, removing accents and performing other changes. It saves the result in normalizedName.
  • You use normalizedName in your join.

As the normalization function is now in java, you should have few restrictions in coding it. Several examples for removing accents in java are given here.

mdma
  • 56,943
  • 12
  • 94
  • 128
  • Unfortunately I don't know how to add new collation on Android phone. – Pentium10 May 15 '10 at 21:04
  • You cannot add a new collation on an Android phone, without modifying the firmware. – CommonsWare May 15 '10 at 21:11
  • I didn't see the android tag. I don't know sqllite well either, but it seems to have support for user defined functions. You could create a UDF that uses a lookup table to map accented chars to non-accented equivalents. Then use this UDF to create a computed column that is the name converted to non-accented chars. You can then join on this column. The computed column is not strictly necessary - you could use the UDF in your JOIN ... ON clause, but I expect it to be more efficient, as an index can be created on the computed column. I hope this is clear - if not I'll write up in more detail. – mdma May 15 '10 at 21:49
  • This is vague, at least point me in the idea you describe, some code would definitely help. – Pentium10 May 15 '10 at 22:00
  • I did offer to provide more details, but saying that this is vague is a little harsh - this is a pointer with the key pieces of the solution listed. Please spend at least as much time reading what I wrote as I have spent in writing it. I don't do Android, so you might do some legwork yourself, such as researching if Android supports the concepts I mention - UDFs, computed columns. If not, then this is a non-starter. Anticipating that, an alternate solution has been added to my answer. – mdma May 16 '10 at 00:11
  • Sorry to be such pain, after rereading and seeing the other answer it is now clear. – Pentium10 May 16 '10 at 18:30
4

There is an easy solution, but not very elegant.

Use the REPLACE function, to remove your accents. Exemple:

SELECT YOUR_COLUMN FROM YOUR_TABLE WHERE replace(replace(replace(replace(replace(replace(replace(replace(
replace(replace(replace( lower(YOUR_COLUMN), 'á','a'), 'ã','a'), 'â','a'), 'é','e'), 'ê','e'), 'í','i'),
'ó','o') ,'õ','o') ,'ô','o'),'ú','u'), 'ç','c') LIKE 'SEARCH_KEY%'

Where SEARCH_KEY is the key word that you wanna find on the column.

Derzu
  • 7,011
  • 3
  • 57
  • 60
1

As mdma says, a possible solution would be a User-Defined-Function (UDF). There is a document here describing how to create such a function for SQLite in PHP. You could write a function called DROPACCENTS() which drops all the accents in the string. Then, you could join your column with the following code:

SELECT * FROM table1
LEFT JOIN table2
ON DROPACCENTS(table1.column1) = DROPACCENTS(table2.column1)

Much similar to how you would use the UCASE() function to perform a case-insensitive join.

Since you cannot use PHP on Android, you would have to find another way to create the UDF. Although it has been said that creating a UDF is not possible on Android, there is another Stack Overflow article claiming that a content provider could do the trick. The latter sounds slightly complicated, but promising.

Community
  • 1
  • 1
littlegreen
  • 7,290
  • 9
  • 45
  • 51
  • Actually, I'm rather skeptical on the approach outlined in the content provider answer you link to. Ms. Hackborn is correct -- `org.sqlite` is not in Android, and it is unclear if there exists an `org.sqlite` implementation that will work in concert with Android's SQLite environment. – CommonsWare May 16 '10 at 00:35
0

Store a special "neutral" column without accented characters and compare / search only this column.

Janeks Bergs
  • 224
  • 3
  • 13