I have a table in my database in SQLITE which has x and y co-ordinates in location field as (10,10).
I want to use the x co-ordinate and y co-ordinate separately by using a select query. I know we can use subtr and charAtIndex to locate the position but I am not sure how to do it ?
I am new to sqlite and need some help to sort this out.
SQLite's string functions are fairly limited but you abuse its implicit type conversions to split your string. Something like this should do it:
select xy + 0 as x, substr(xy, length(xy + 0) + 2) as y from some_table
That should work as long as you don't have any leading zeros or spaces or internal spaces. Adding zero turns the value into a number so
'10,5' + 0 becomes just
10; then calling
length on that turns it back into a string and gives you the string length, then adjust that to account for the comma (
+1) and length-to-offset (
+1) change and you'll get your 5 out of
sqlite> select '10,5' + 0 as x, substr('10,5', length('10,5' + 0) + 2) as y; x|y 10|5
That said, you really should redesign your schema to have separate columns for the X and Y coordinates, storing structured data inside a single column is generally a bad idea.
Update for comments: SQLite will also let you do things like this:
select value, xy + 0 as x, substr(xy, length(xy + 0) + 2) as y from some_table where x < 2000 and y < 2000
select value from some_table where (xy + 0) < 2000 and substr(xy, length(xy + 0) + 2) < 2000
These sorts of queries will end up doing full table scans though so they might be too slow for you. Fixing your schema to have separate X and Y coordinate columns OTOH would give you fast queries.