lifemoveson lifemoveson - 1 year ago 79
Objective-C Question

how to use substr for comma separated values in table in sqlite

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.

Answer Source

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 '10,5'.

For example:

sqlite> select '10,5' + 0 as x, substr('10,5', length('10,5' + 0) + 2) as y;

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

or even:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download