0

I am working on an SMS application. I have designed the database so that my application does not get broken when the SMS content provider is removed. I have implemented some triggers to do clean-up work. I created the sms class using this statement:

`CREATE TABLE sms(_id INTEGER PRIMARY KEY AUTOINCREMENT, date_sent INTEGER DEFAULT 0, address TEXT, body TEXT, thread_id INTEGER);`

When the user would receive an SMS, the address of the SMS would contain his number plus country code. For me, it can be +91 9807 xxx xxx.

Suppose my table stores this address as 9807 xxx xxx. In case of triggers, a newly inserted row can be accessed with new keyword. So, to access the address, I would write new.address.

The Trigger is programmed in a way that depends on address and trigger only avails address information as new.address only.

My question is: how can I compare 9807 xxx xxx stored in the database against +91 9807 xxx xxx ?

Please note that I do not have direct access to either 9806 xxx xxx or +91 9807 xxx xxx. I can only reference them using address and new.address respectively.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
Gaurav
  • 3,614
  • 3
  • 30
  • 51
  • Do you need SQL or Java expression? – asktomsk Sep 11 '13 at 18:14
  • I need SQL expression. – Gaurav Sep 11 '13 at 18:21
  • You should not save them with and without country code mixed because it is nearly impossible to compare them then. Country codes have 1 to 3 or 4 numbers and that makes it extremely hard. Android's SQLite has a hidden `PHONE_NUMBERS_EQUAL` function that you could use if you store them in a unified way. – zapl Sep 11 '13 at 18:53
  • @zapl: can you provide an illustration for `PHONE_NUMBERS_EQUAL' ? – Gaurav Sep 11 '13 at 18:54
  • 2
    [here](http://stackoverflow.com/a/2175483/995891) are some details about that function. It does not work on `+91 9807` vs `9807` but it works on `+91 9807` vs `+91-98-07`. It's used like any SQLite function: `DELETE FROM table WHERE PHONE_NUMBER_EQUAL(new.address, address)` – zapl Sep 11 '13 at 19:00
  • @zapl: Well, I am receiving error for PHONE_NUMBER_EQUAL and PHONE_NUMBERS_EQUAL. It says no_such_function. – Gaurav Sep 11 '13 at 20:40

0 Answers0