SQLite – Matching Separate Unicode Characters as One

csqlite

I am writing a C# program, and I'm querying a SQLite database. I have multiple diacritical characters (e.g ã, õ, ó, ê) which I need to match in my search. I have the command

"SELECT * FROM DesanoDict1 WHERE Desano LIKE '%" + string + "%' ORDER BY Desano ASC"

However, I have the problem where those diacritical characters are entered in as separate ones (i.e the ã is an a + ~). I could make a custom keyboard with the single unicode characters (as one instead of two separate characters), but the users that will be using the program will probably be using either the same or a similar keyboard to the one I'm currently using, so it will only create problems. I am very new to database and querying, so any help here would be greatly appreciated.

Best Answer

Unicode is a complex beast and database engines tend to be at best superficially aware of it. They certainly don't tend to know about sequence equivalences.

I would suggest making sure that you store data in a consistent normalised form, and normalise search parameters in the same way before searching. I'm not really a c# person, but https://msdn.microsoft.com/en-us/library/ebza6ck1(v=vs.110).aspx looks like a decent place to start for how to go about that.

Obviously you will have existing data that is not normalised, so if you go with this route you'll need to write something to scan the data and replace string values with normalised versions as needed.

Related Solutions

SQLite – When to Use Index Types Other Than Integer (e.g., String)

There are a lot of places internally where it is possible to reference data using a user id, which is better than a string. For example, if you had 4 tables referencing the user table, they'd each have a string column which is less efficient than 4 integer indexed columns. This is the reason why most schemas use this layout; you're more likely to want to reference a user than to refer to the user by name.

However, you can also set username to be an index, which you might want to do if you are looking it up a lot.

To answer your edited question, a 24 character text field will require roughly 24 bytes. Depending on whether it's a varchar or a char it will be stored differently, but the full amount will be needed for searching, so let's consider them both to be 24.

An integer field will require 4 bytes.

You can see how this will add up over time. For comparisons, assume a linear time comparison, meaning 24 operations to compare two a string field, while only 4 operations to compare an integer field. In practice, they will probably take roughly the same amount of time, though the string comparison cannot be faster.

If you want to search by username, create a table with the schema of username | user_id. That'll let you search by username and join with the other data you are looking for while still being efficient.

SQLite – How to Get Data According to Last Hours

Finally I found the solution to my own question

Following is the code which worked for me

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        String startDate = dateFormat.format(date);
        String endDate = "";
        for (int i = 1; i <= 24; i++) {
            System.out.println("Start Date:- " + startDate);
            calendar.add(Calendar.HOUR_OF_DAY, -1);
            date = calendar.getTime();
            endDate = dateFormat.format(date);
            System.out.println("End Date:- " + endDate);
            String data = dbAdapter.getOutDoorHourlyData(startDate, endDate);
            System.out.println("Hourly Average:- " + data);
            startDate = endDate;
            endDate = "";
        }

public String getOutDoorHourlyData(String startDate, String endDate) {
        double outdoorHourly = 0;

        Cursor cursor = _sqliteDB.rawQuery("Select AVG("
                + COLOUMN_NAME + ") from (Select * FROM "
                + TABLE_NAME + " where " + COLOUMN_NAME + " >= '"
                + endDate + "' and " + COLOUMN_NAME + " < '" + startDate
                + "')", null);

        try {

            if (cursor != null) {
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    do {
                        outdoorHourly = cursor.getDouble(0);
                    } while (cursor.moveToNext());
                }
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        String hourlyData = decimalFormat.format(outdoorHourly);
        hourlyData = hourlyData.replace(",", ".");
        return hourlyData;

    }

 }

I hope it will help someone in future

Best Answer

Related Solutions

SQLite – When to Use Index Types Other Than Integer (e.g., String)

SQLite – How to Get Data According to Last Hours

Related Question