How to read array, which is saved as string, and work with array?Ask Questions

 Posted on 22/08/2018 10:54:34 AM

For some reason my .db file has a table1 with a Column1, which data are stored as TEXT:

'[1,   2,   0.5]'
'[50,  8.5, 30]'
'[12,  14,  0.5]'
'[5,   45,  25]'
'[0.2, 10,  25]'

These data are in TEXT format, although they look like Array.

I want to use the data as array.

To be exactly: I want to DELETE the rows, which second item is less than 15. The desired result should be:

'[5,   45,  25]'

I tried a lot of methods on my own, but none of them worked. One of my many tries looks like: (I admit I have absolutely no idea how to do this task.)

DELETE FROM table1 WHERE Column1 <= 15;

Could anybody please help me out? Thanks so much!

Share On: facebook gplus twitter
Asked by Hardik Chaudhary on 22/08/2018 10:54:34 AM Score: 375 points
Add Comment:


1 Answer

1 Corrected Answers
Aproved Answers
Answered by Shailesh Chaudhary on Sep 22 2018 7:14AM Score: 543 points

#Hardik Chaudhary you can use JSON.

However, it does have support for working with JSON (Assuming your sqlite instance was compiled with the JSON1 extension, of course). And your sample values happen to be valid JSON arrays.

That gives us something to work with:

DELETE FROM table1 WHERE json_extract(column1, '$[1]') < 15;
Add Comment:

Post Your Answer


Existing Members

Sign in to your account
Email Address
...or Join us
Download, Vote, Comment, Publish.
Full Name
Email Address
I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the StoodQ newsletters
Guideline to answer a question:

Useful tips to submit your answer
Please read below guidelines before you submit your answer for question.

  • Read and understand question for which you are submitting your answer.
  • Try to avoid grammatical and spell mistake while answering.
  • Do not post any irrelevant information in your answer.
  • Explain your answer with example or any reference link to help who posted question.
  • If you find irrelevant question, please report it to support. Click here to contact support.
  • You agree to the privacy policy and terms of use to submit any contents.

Note: StoodQ is online developers community which helps developer for their difficulty, lets help them with your value contribution.