sqlite order by string containing number with comma delimiter

Question

I have a column that contains Sring data with numbers in them. the format of the number is like (x,xxx). for example, the number 1555 is stored as 1,555 I want to order the data from the table. I have tried CAST AS

@Query("SELECT * from  country_table ORDER BY CAST(cases AS INTEGER)")
fun getAllCountries(): LiveData<List<SingleCountryStats>>

but this doesn't work since the field can not be cast to integer due to the comma in the middle

is there any option to remove the commas then order the results?

Answer 1

If the commas are used consistently, you can use:

order by length(cases), cases

Otherwise, you can remove the commas and convert:

order by cast(replace(cases, ',', '') as int)