I would like to create a SELECT query that would return numbers from column in integer format as a text format - can I do it in SQLite?
Add a comment
|
1 Answer
SQLite supports CAST and:
Casting an INTEGER or REAL value into TEXT renders the value as if via sqlite3_snprintf() except that the resulting TEXT uses the encoding of the database connection.
So you can do things like this:
select cast(some_integer_column as text) from some_table;
Or, depending on what you're trying to do, you could just treat the numbers as strings and let SQLite coerce the types as it sees fit:
select some_int || ' pancakes' from some_table;
select some_int || '' from some_table;
5 Comments
Martijn Pieters
Interestingly enough, the official documentation consistently gets the syntax wrong as
CAST(<expr> TO <type>) (note the TO in there).
mu is too short
@Martijn: I didn't even notice that in the docs as I'm used to the standard as syntax. How many times does the official documentation list the full CAST syntax? I only see one mention but I guess that counts as consistent :)
Martijn Pieters
The syntax diagram is correct, but the changelog for 2.8.2 also uses the wrong syntax. 2 out of 3 uses on the whole site that spell out the syntax are wrong though; but I may have exaggerated a little. :-) I reported it to the project.
Brad
Seems that
CAST(... TO ...) doesn't work, but AS does? At least as of the writing of this comment.mu is too short
@Brad Looks like the
cast(... to ...) was a typo in the documentation at some point. The "CAST expressions" docs (currently) use AS, the syntax diagram uses AS, the SQL standard uses AS, and AS even works :)