SQLite language independent query of numbers

Question

I have written an android app for fun that have inner sqlite database after gathering many data using html parser I found my numbers that are saved as text in database, are written in English language, so doing query in persian that people in my country try will return nothing on numbers

String q = "SELECT * FROM studentIDs WHERE field1  LIKE '%"+name+"%' OR field2  LIKE '%"+name+"%'";

while doing good on both field1 that is string ,it won't work on field2 that number stored as string, how should I perform language independent query on numbers?

I can't change characters from English to other I want support for both languages and I can't change it's type to integer because some records are English name

Sorry about my English and thanks in advance

Answer 1

Since your data type is String, it means you can store any character sequence to it(depends on your sqlite encoding config e.g utf8) and Sqlite doesn't care and shouldn't care about it.

You have a simple solution here:

Just write a simple mapper before any query to database:

String mapToEn(String query) {
    return query
            .replace('۰', '0')
            .replace('۱', '1')
            .replace('۲', '2')
            .replace('۳', '3')
            .replace('۴', '4')
            .replace('۵', '5')
            .replace('۶', '6')
            .replace('۷', '7')
            .replace('۸', '8')
            .replace('۹', '9');
}

And use it on your query or query parameters before executing the query to database:

Result query(mapToEn(query));

Edit:

Since you said

I can't change it's type to integer cause some recored's are english name

I thought your data in the field1 is a combination of numbers and characters, now that you clarified it only contains numeric or String data you have another solution.

Database Schema Migration

Since your database schema doesn't match your requirements anymore you have to make some changes to it. You need to differentiate the data type you have entered, simply adding two new column as field_str and field_num.

Basically you should write a database migration which is responsible for converting the field1 column's data from String to Integer if its an Integer without losing any data, Here are the steps you should do:

1. Add an Integer and a String column to your table respectively field_num and field_str.
2. Iterate through the table and parse all those Strings in field1 to Integer and insert them into 'field_numcolumn, and insert the unparseable ones intofield_str` column.
3. change your query accordingly.

Since sqlite does not support column drop, You either have to add a new column to your existing table and leave alone the old data to be there, or You can create a new table and migrate all of your data to the new table:

Here is some hypothetical situation:

sqlite> .schema
CREATE TABLE some_table(
 id INTEGER PRIMARY KEY, 
 field1 TEXT,
 field2 TEXT, 
);
sqlite> select * from some_table; 
id          field1      field2
----------  ----------  ------
0           1234         name<br>   
1           bahram       name  

Now create another table

sqlite> CREATE TABLE new_some_table(
   ...>  id INTEGER PRIMARY KEY, 
   ...>  field_str TEXT, 
   ...>  field_num INTEGER,
   ...>  field2 TEXT,
   ...> ) ; 

Now copy your data from the old table

sqlite> INSERT INTO new_some_table(id, field_str, field2)
   ...> SELECT id, field1, field2, FROM some_table ;

sqlite> INSERT INTO new_some_table(id, field_num)
   ...> SELECT id, field1, FROM some_table WHERE typeof(field1) = "integer" ;

Now you can query your table based on what type of data you have.

Consider using an ORM which provides the migration tool, like Google's Room or dbflow.