SQLite equivalent of Python's "'%s %s' % (first_string, second_string)"

Question

As the title says, what is the equivalent of Python's '%s %s' % (first_string, second_string) in SQLite? I know I can do concatenation like first_string || " " || second_string, but it looks very ugly.

Answer 1

There isn't one.

Answer 2

I can understand not liking first_string || " " || second_string, but that's the equivalent. Standard SQL (which SQLite speaks in this area) just isn't the world's prettiest string manipulation language. You could try getting the results of the query back into some other language (e.g., Python which you appear to like) and doing the concatenation there; it's usually best to not do "presentation" in the database layer (and definitely not a good idea to use the result of concatenation as something to search against; that makes it impossible to optimize with indices!)

Answer 3

I am not entirely sure what you are looking for, but it might be the group_concat aggregate function.

Answer 4

Are you sure you're not looking for parameter substitution?

Directly from the sqlite3 module documentation:

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)

For example:

# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

Answer 5

Note you can create your own SQL level functions. For example you could have this Python function:

def format_data(one, two):
    return "%s %s" % (one, two)

Use pysqlite's create_function or APSW's createscalarfunction to tell SQLite about it. Then you can do queries like these:

SELECT format_data(col1, col2) FROM table WHERE condition;
SELECT * from TABLE where col1 = format_data('prefix', col2);

Consequently you can put the formatting logic in your nice readable Python code while keeping the SQL simple but clearly showing the intent.