How to convert a string to a real number in sql?
  • Attention - in SQL! DBMS - firebird 2.0 - 2.5 Preferred - procedure code. Required for use in queries (select construction) - Sherl

2 answers 2

set term ^; create or alter procedure str2dbl(str varchar(20)) returns (dbl double precision) as begin dbl=null; begin begin dbl=cast(str as double precision); end when any do begin end end suspend; end ^set term ;^ commit; ----------------- -- Test: C:\1INSTALL\FIREBIRD\Data>isql -n TESTX.fdb Database: TESTX.fdb SQL> select * from str2dbl('111.23') CON> union all CON> select * from str2dbl('11152.') CON> union all CON> select * from str2dbl('.88819') CON> union all CON> select * from str2dbl('44,333') CON> union all CON> select * from str2dbl('......') CON> union all CON> select * from str2dbl('йцукенг'); DBL ======================= 111.2300000000000 11152.00000000000 0.8881900000000000 <null> <null> <null>

    Try a standard design, for example:

    select cast ('12 .35 'as dec (6.2))

    • It will not work - you yourself wrote a line through a dot in the code. On it and stumble. - Sherl
    • Actually, the result: Arithmetic overflow or division by zero has occurred. arithmetic exception, numeric overflow, or string truncation. Cannot transliterate character between character sets. - Sherl
    • If this is a problem of language settings, then it will work with a comma. Or explicitly use the appropriate COLLATE. - msi