Sqlitesql查询数据数据类型转换⼀、SQL数据类型转换代码
SELECT CAST('3.02' as decimal)
-- they aren't real decimals in sqlite, though. beware floats.
⼆、sqlite数据类型转换参考
Affinity
of type-
name
Conversion Processing  转换处理
NONE 没有
Casting a value to a type-name with no affinity causes the value to be converted into a BLOB. Casting to a BLOB consists of first casting the value to TEXT in the  of the database connection, then interpreting the resulting byte sequence as a BLOB instead of as TEXT.
将⼀个值转换为⼀个 类型名称 没有关联将导致值转换为BLOB。强制转换为BLOB包括⾸先将值强制转换为 编码 然后将结果字节序列解释为BLOB⽽不是⽂本。
TEXT ⽂本
To cast a BLOB value to TEXT, the sequence of bytes that make up the BLOB is interpreted as text encoded using the database encoding.
Casting an INTEGER or REAL value into TEXT renders the value as if via  except that the resulting TEXT uses the  of the database connection.
若要将BLOB值强制转换为⽂本，组成BLOB的字节序列将被解释为使⽤数据库编码的⽂本编码。将整数值或实数值强制转换为⽂本将使该值如同通过 sqlite3\u snprintf（） 除了结果⽂本使⽤ 编码 数据库连接的名称。
REAL 真
When casting a BLOB value to a REAL, the value is first converted to TEXT.
When casting a TEXT value to REAL, the longest possible prefix of the value that can be interpreted
as a real number is extracted from the TEXT value and the remainder ignored. Any leading spaces in the TEXT value are ignored when converging from TEXT to REAL. If there is no prefix that can be interpreted as a real number, the result of the conversion is 0.0.
将BLOB值强制转换为实数时，⾸先将该值转换为⽂本。
将⽂本值强制转换为实数时，将从⽂本值中提取可解释为实数的值的最长前缀，并忽略其余部分。从⽂本聚合到实数时，将忽略⽂本值中的任何前导空格。如果没有可以解释为实数的前缀，则转换的结果为0.0。
整数
When casting a BLOB value to INTEGER, the value is first converted to TEXT.
When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored. Any leading spaces in the TEXT value when converting from TEXT to INTEGER are ignored. If there is no prefix that can be interpreted as an integer number, the result of the conversion is 0. If the prefix integer is greater than
+9223372036854775807 then the result of the cast is exactly +9223372036854775807. Similarly, if the prefix integer is less than -9223372036854775808 then the result of the cast is exactly -9223372036854775808.
When casting to INTEGER, if the text looks like a floating point value with an exponent, the exponent will be ignored because it is no part of the integer prefix. For example, "(CAST '123e+5' AS INTEGER)" results in 123, not in 12300000.
The CAST operator understands decimal integers only — conversion of  stops at the "x" in the "0x" prefix of the hexadecimal integer string and thus result of the CAST is always zero.
INTEGER the hexadecimal integer string and thus result of the CAST is always zero.
A cast of a REAL value into an INTEGER results in the integer between the REAL value and zero that is closest to the REAL value. If a REAL is greater than the greatest possible signed integer (+9223372036854775807) then the result is the greatest possible signed integer and if the REAL is less than the least possible signed integer (-9223372036854775808) then the result is the least possible signed integer.
Prior to SQLite version 3.8.2 (2013-12-06), casting a REAL value greater than +9223372036854775807.0 into an integer resulted in the most negative integer, -9223372036854775808. This behavior was meant to emulate the behavior of x86/x64 hardware when doing the equivalent cast.
将BLOB值强制转换为整数时，⾸先将该值转换为⽂本。
将⽂本值强制转换为整数时，将从⽂本值中提取可解释为整数的值的最长前缀，并忽略余数。从⽂本转换为整数时，⽂本值中的任何前导空格都将被忽略。如果没有可解释为整数的前缀，则转换结果为0。如果前缀整数⼤于
+9223372036854775807，则强制转换的结果正好是+9223372036854775807。类似地，如果前缀整数⼩于-9223372036854775808，则强制转换的结果正好是-9223372036854775808。
强制转换为整数时，如果⽂本看起来像带指数的浮点值，则指数将被忽略，因为它不是整数前缀的⼀部分。例
如，“（将'123e+5'转换为整数）”结果是123，⽽不是12300000。
decimal是整数数据类型CAST运算符只理解⼗进制整数-的转换 ⼗六进制整数 在⼗六进制整数字符串的“0x”前缀中的“x”处停⽌，
因此强制转换的结果始终为零。
将实值转换为整数将导致实值和零之间的整数最接近实值。如果实数⼤于最⼤可能有符号整数
（+9223372036854775807），则结果为最⼤可能有符号整数，如果实数⼩于最⼩可能有符号整数（-9223372036854775808），则结果为最⼩可能有符号整数。
在SQLite版本3.8.2（2013-12-06）之前，将⼤于+9223372036854775807.0的实值强制转换为整数会导致最负的整数-9223372036854775808。此⾏为旨在模拟执⾏等效转换时x86/x64硬件的⾏为。
NUMERIC 数字
Casting a TEXT or BLOB value into NUMERIC yields either an INTEGER or a REAL result. If the input text looks like an integer (there is no decimal point nor exponent) and the value is small enough to fit in a 64-bit signed integer, then the result will be INTEGER. Input text that looks like floating point (there is a decimal point and/or an exponent) and the text describes a value that can be losslessly converted back and forth between IEEE 754 64-bit float and a 51-bit signed integer, then the result is INTEGER. (In the previous sentence, a 51-bit integer is specified since that is one bit less than the length of the mantissa of an IEEE 754 64-bit float and thus provides a 1-bit of margin fo
r the text-to-float conversion operation.) Any text input that describes a value outside the range of a 64-bit signed integer yields a REAL result.
Casting a REAL or INTEGER value to NUMERIC is a no-op, even if a real value could be losslessly converted to an integer.
将⽂本或BLOB值转换为数字将产⽣整数或实数结果。如果输⼊⽂本看起来像⼀个整数（没有⼩数点或指数），并且该值⾜够⼩，可以容纳64位有符号整数，那么结果将是整数。输⼊看起来像浮点的⽂本（有⼀个⼩数点和/或⼀个指数），该⽂本描述了⼀个值，该值可以在IEEE 754 64位浮点和51位有符号整数之间⽆损地来回转换，然后结果是整数(在前⾯的句⼦中，指定了⼀个51位整数，因为它⽐ieee754 64位浮点的尾数长度少⼀位，因此为⽂本到浮点的转换操作提供了1位的余量。）任何描述64位有符号整数范围之外的值的⽂本输⼊都会产⽣⼀个实际结果。
将实数或整数值转换为数值是不可操作的，即使实数可以⽆损地转换为整数。
decimal
⼗进制

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。