SQLite Forum

Is this inherent casting by design?

Is this inherent casting by design?

(1) By anonymous on 2021-11-04 07:33:40 [link] [source]

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

(2) By Bill Wade (billwade) on 2021-11-04 11:41:17 in reply to 1 [source]

The behavior is documented.

"Mathematical operators (+, -, *, /, %, <<, >>, &, and |) interpret both operands as if they were numbers. STRING or BLOB operands automatically convert into REAL or INTEGER values."

The rules for converting strings to numbers roughly take the longest prefix from the string that looks like a number.

(3) By Gunter Hick (gunter_hick) on 2021-11-04 11:56:24 in reply to 1 [link] [source]

See https://sqlite.org/datatype3.html

"Mathematical operators (+, -, *, /, %, <<, >>, &, and ) interpret both operands as if they were numbers."

And no, the string has to start with something that looks like a number (leading whitespace is allowed). So '10 EUR' converts to 10, whereas 'EUR 10' converts to 0.

This conversion is a convenience feature due to permissive typing; strictly typed engines may even throw a syntax error, telling you that multiplying a string and a number is unsupported.

(4) By Kees Nuyt (knu) on 2021-11-04 12:01:50 in reply to 1 [link] [source]

Use the ltrim() function to remove leading characters.

Kees Nuyt