Convert Integer to Text in SQLite's Select Query

Convert integer to text in SQLite's SELECT query?

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;

.0 appended when converting number to text in SQLITE

You can check whether removing the fractional digits would change the value:

SELECT CAST(CASE WHEN CAST(orderid AS INTEGER) = orderid
                 THEN CAST(orderid AS INTEGER)
                 ELSE orderid
            END AS TEXT)
FROM orders;

Converting text to int in sqlite when querying

You need to cast in where clause, not where you are selecting it.

string sql4 = "select seq, maxLen from abc where CAST(maxLen as INTEGER) > 30";

Also in your current cast version it will not work since CAST works for a single field.

For your question:

How would I also convert the text to double

cast it to REAL like:

CAST(maxLen as REAL)

How to convert the datatype from Integer to Text?

It is easiest to make placeholders for the query outside the cur.execute.
Both of your in queries need n comma separated question marks.

>>> to_find = "15 3 44 9"  # String format, but all numbers, with empty spaces
>>> to_find = to_find.split() # Convert TextTemp into TEXT format
>>> to_find
['15', '3', '44', '9']
>>> placeholders = ','.join('?' * len(to_find))
>>> placeholders
'?,?,?,?'

Then we fill in the query. I will use .format instead of the old-style formatting as it is easier to substitute the same value many times with the new-style formatting:

>>> query = '''CREATE TABLE newTable AS  
               SELECT * FROM oldTable 
               WHERE ColA IN ({0})
               OR ColB IN ({0})'''.format(placeholders)
>>> print(query)
CREATE TABLE newTable AS  
               SELECT * FROM oldTable 
               WHERE ColA IN (?,?,?,?)
               OR ColB IN (?,?,?,?)

As each ? is bound to a distinct parameter from the parameter list and we now have 2 * n placeholders, we need to duplicate the substituted values too, hence tofind * 2:

>>> to_find * 2
['15', '3', '44', '9', '15', '3', '44', '9']
>>> cur.execute(query, to_find * 2)

And here's the code in final compact form

to_find = to_find.split()
placeholders = ','.join('?' * len(to_find))
query = '''CREATE TABLE newTable AS  
           SELECT * FROM oldTable 
           WHERE ColA IN ({0})
           OR ColB IN ({0})'''.format(placeholders)
cur.execute(query, to_find * 2)

converting int to real in sqlite

Just multiply one of the numbers by 1.0:

SELECT something*1.0/total FROM somewhere

That will give you floating point division instead of integer division.

SQLite: insert as Integer and as Text

SQLite tries to convert your String into an Integer before inserting the value into your table as described in the manual.

The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another.

Convert column values into columns and select other column

You need aggregate function MAX():

SELECT Document,
       COALESCE(MAX(CASE WHEN Entity = 'art' THEN wordFrequency END), 0) AS Art,
       COALESCE(MAX(CASE WHEN Entity = 'action' THEN wordFrequency END), 0) AS Action,
       COALESCE(MAX(CASE WHEN Entity = 'puzzle' THEN wordFrequency END), 0) AS Puzzle
FROM __table__
GROUP BY Document;

If the data type of the column wordFrequency is TEXT, use instead:

... THEN wordFrequency + 0 END

to convert it implicitly to a number.

Convert string to int inside WHERE clause of SQLITE statment

Use CAST(reputation AS INTEGER).

CAST TEXT as INTEGER

If you are trying to ignore commas, then remove them before the conversion:

select cast(replace('323,999', ',', '') as integer)

---

Related Topics