Regular expression using sqlite in android

Question

I having a difficulty with regex, I have a table products that of one of it`s field is a period. I want to filter records by a period the user entered in my application. for example the period might be 2006-2012 or just 2006-

I want to filter all the records that contain only the pattern 2006-

I tried many examples but unfortunately nothing works properly

this is my code

Cursor c = rDB.rawQuery("select title,price from Products,UserProducts where UserProducts.product_code=Prodcuts.product_code and USER_EMAIL=? and Products.period like '%[0-9]+%' group by UserSeries.product_code order by Products.title asc", new String[]{email});
while(c.moveToNext())
{
         //save the records 
}

laalto · Accepted Answer · 2014-01-03 16:26:28Z

4

Products.period like '%[0-9]+%'

LIKE does not work with regexps.

To use regular expression matching, use the REGEXP operator. Note that by default the REGEXP operator is not implemented, even though the syntax supports it.

For simpler matching needs you can also consider GLOB.

I want to filter all the records that contain only the pattern 2006-

You don't need a regular expression for that.

Products.period like '2006-%'

will match anything where period starts with the string 2006-.

I meant any year with "-" symbol at the end

Products.period like '%-'

will match anything where period ends with the string -.

edited Jan 3, 2014 at 16:26

answered Jan 3, 2014 at 16:14

laalto

153k68 gold badges293 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anton Makov Over a year ago

I meant any year with "-" symbol at the end, with regexp i am getting an exception exception android.database.sqlite.SQLiteException: near "REGEX": syntax error (code 1) and with glob i am not getting any records from the cursor

laalto Over a year ago

REGEX is not REGEXP. But it's not supported anyway. GLOB pattern syntax is different and similar to LIKE in terms of expressive power. Updated the answer.

Anton Makov Over a year ago

I checked my table again and strangely the periods contain ΓÇô and not - character

laalto Over a year ago

To me that looks like some other dash character UTF-8 octets interpreted as ISO-8859-x.

Anton Makov Over a year ago

I tried many combinations with ΓÇô but nothing seems to work properly like '%ΓÇô' , like 'ΓÇô', in any case i don`t get any records

Collectives™ on Stack Overflow

Regular expression using sqlite in android

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related