SQL Query: select only numbers from a list of string values

Question

I need to create a query in an sqlite database using DB Browser. The columns are: state, measure_id, measure_name, score. All of the data has been input as strings. I can cast the score strings as decimal, however the problem is that some of the values for the score column are numeric and some are actual string values (such as "high" etc). I need to ignore the REAL string values in my output. Also, I need to calculate the standard deviation (as well as min,max,avg) for each measure_id.

How can I ignore the real string values and calculate the standard deviation?

Here is some sample data:

sample 1: AL, ID1, Ident, 52
sample 2: TX, ID2, Foo, High
sample 3: MI, ID3, Bar, 21

(I want to select only sample 1 and 3, and then cast the strings as int and calculate stdev)

Answer 1

If the values are never 0, you can do:

select avg(cast(value as decimal))
from t
where cast(value as decimal) > 0;

The standard deviation is a bit trickier to calculate. You can use the defining formula, but SQLite doesn't even have a square root function.

You might want to move to another database, such as Postgres or MySQL, if you want to support these types of operations.