Sometimes there is a need to combine data from different columns into one string. For example,
SELECT CONCAT(first_name, ' ', last_name) FROM employees;This doesn’t look too bad, but can quickly get out of hand, if you need to do something more complex than that. For example, let’s say, we also need to mention the salary here:
SELECT CONCAT(first_name, ' ', last_name, ' -> ', CAST(FORMAT(salary, 0) AS VARCHAR(10)), ' ', currency) FROM employees;This preview shows a new feature for MariaDB 10.7 that was developed as a part of Google Summer of Code (MDEV-25015) by Alan Cueva, together with his mentor Vicențiu Ciorbaru. A new SQL function SFORMAT, that uses Python-like (or Rust-like, C++20 like, make your pick) format syntax.
Using the new SFORMAT function the previous example can be written as
SELECT SFORMAT('{} {} -> {:L} {}', first_name, last_name, salary, currency) FROM employees;In a sense, it can be seen as a more convenient version of CONCAT. Similarly, it can be used to generate the SQL on the fly:
SELECT SFORMAT('select * from `{}` where `{}` = {}', table_name, cond_column_name, QUOTE(value)) FROM reports;But this function can so much more that was very difficult or practically impossible to do with SQL before. The implementation uses the fmt.dev formatting library, and the complete syntax can be found there.
How to try the 10.7 sformat preview yourself?
Tarball
Go to tarball downloads.
Container
Use the container quay.io/mariadb-foundation/mariadb-devel:10.7-mdev-25015-sformat.
Binder online MariaDB Jupyter Notebook
Or try the feature online in Binder.
Feedback Welcome
If you come across any problems in this feature preview, with the design, or edge cases that don’t work as expected, please let us know with a JIRA bug/feature request in the MDEV project. You are welcome to chat about it on Zulip.