SQL order string as number

It might help who is looking for the same solution.

select * from tablename ORDER BY ABS(column_name)

This works for me.

select * from tablename
order by cast(columnname as int) asc

If you are using AdonisJS and have mixed IDs such as ABC-202, ABC-201..., you can combine raw queries with Query Builder and implement the solution above (https://stackoverflow.com/a/25061144/4040835) as follows:

const sortField =
  'membership_id'
const sortDirection =
  'asc'
const subquery = UserProfile.query()
  .select(
    'user_profiles.id',
    'user_profiles.user_id',
    'user_profiles.membership_id',
    'user_profiles.first_name',
    'user_profiles.middle_name',
    'user_profiles.last_name',
    'user_profiles.mobile_number',
    'countries.citizenship',
    'states.name as state_of_origin',
    'user_profiles.gender',
    'user_profiles.created_at',
    'user_profiles.updated_at'
  )
  .leftJoin(
    'users',
    'user_profiles.user_id',
    'users.id'
  )
  .leftJoin(
    'countries',
    'user_profiles.nationality',
    'countries.id'
  )
  .leftJoin(
    'states',
    'user_profiles.state_of_origin',
    'states.id'
  )
  .orderByRaw(
    `SUBSTRING(:sortField:,3,15)*1 ${sortDirection}`,
    {
      sortField: sortField,
    }
  )
  .paginate(
    page,
    per_page
  )

NOTES: In this line: SUBSTRING(:sortField:,3,15)*1 ${sortDirection},

1. '3' stands for the index number of the last non-numerical character before the digits. If your mixed ID is "ABC-123," your index number will be 4.
2. '15' is used to catch as any number of digits as possible after the hyphen.
3. '1' performs a mathematical operation on the substring which effectively casts the substring to a number.

Ref 1: You can read more about parameter bindings in raw queries here: https://knexjs.org/#Raw-Bindings Ref 2: Adonis Raw Queries: https://adonisjs.com/docs/4.1/query-builder#_raw_queries