CODEWITHSUNDEEP
androidsqlsqliteandroid-sqlitesql-order-by
Remon Shehatta
Remon Shehatta

Reputation: 1480

sqlite order by string containing number with comma delimiter

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?

Upvotes: 2

Views: 780

Answers (2)

forpas
forpas

Reputation: 164139

Remove the comma and sort the column as a number:

SELECT * from  country_table ORDER BY REPLACE(cases, ',', '') + 0

The operator + casts implicitly the string to a number.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

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)

Upvotes: 2

Related Questions