50

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?

mu is too short
  • 426,620
  • 70
  • 833
  • 800
syntagma
  • 23,346
  • 16
  • 78
  • 134

1 Answers1

107

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;
xhienne
  • 5,738
  • 1
  • 15
  • 34
mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 2
    Interestingly enough, the official documentation consistently gets the syntax wrong as `CAST( TO )` (note the *TO* in there). – Martijn Pieters Jan 29 '14 at 11:46
  • @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 :) – mu is too short Jan 29 '14 at 17:59
  • 2
    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. – Martijn Pieters Jan 29 '14 at 18:00
  • Seems that `CAST(... TO ...)` doesn't work, but `AS` does? At least as of the writing of this comment. – Brad Apr 24 '19 at 15:53
  • @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 :) – mu is too short Apr 24 '19 at 17:28