SQLite Forum

Is this inherent casting by design?
It appears that when multiplying a string that starts with numbers by 1 (and, presumably, any other math operation would also work) you effectively convert it to a number.

Multiplying by 1 (for example) has the welcome side effect of extracting just the number and/or allowing numeric ordering even when the field does not contain a pure number:

create table t(v);

insert into t values
  ('300 EUR'),
  ('30 EUR'),
  ('300 USD'),
  ('30 USD'),
  ('5 USD'),
  ('5 EUR');

select v normal from t order by v; --normal string ordering
select v numeric,v*1 from t order by v*1; --numeric ordering

I'd like to know if

1. this is by design (and, therefore, can be relied upon to not change in the future), and

2. if (by design) it could be expanded to work with the first number regardless of position within the string, so that:

insert into t values
  ('EUR 300'),
  ('EUR 30'),
  ('USD 300'),
  ('USD 30'),
  ('USD 5'),
  ('EUR 5');

select v numeric,v*1 from t order by v*1;

would also extract the number (`v*1`) and come out sorted numerically.

Thank you