[MDEV-25015] Custom formatting of strings in MariaDB queries - Jira

Robert Bindar created issue - 2021-03-01 11:55

Robert Bindar made changes - 2021-03-01 11:56

Field	Original Value	New Value
Description	Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved. It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification. A great example for such a function is the classic C printf function, which, in this context, would look something like: SELECT printf('%s %s %s', first_name, last_name, job_title) from employees; But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme: SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table; Also this one has the advantage of being able to pass formatting options within the curly braces: # Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021' SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table; Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.	Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved. It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification. A great example for such a function is the classic C printf function, which, in this context, would look something like: ```SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;``` But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme: SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table; Also this one has the advantage of being able to pass formatting options within the curly braces: # Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021' SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table; Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Robert Bindar made changes - 2021-03-01 11:57

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

```SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;```

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;

Also this one has the advantage of being able to pass formatting options within the curly braces:

# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;

Also this one has the advantage of being able to pass formatting options within the curly braces:

# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Robert Bindar made changes - 2021-03-01 11:58

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;

Also this one has the advantage of being able to pass formatting options within the curly braces:

# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Robert Bindar made changes - 2021-03-01 11:59

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many concat(), format(), etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a SELECT statement can get awkward when there are many {noformat}concat(){noformat}, {noformat}format(){noformat}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Robert Bindar made changes - 2021-03-01 12:00

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many {noformat}concat(){noformat}, {noformat}format(){noformat}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a SELECT statement can get awkward when there are many {{monospaced}}concat(){{monospaced}}, {{monospaced}}format(){{monospaced}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Robert Bindar made changes - 2021-03-01 12:00

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many {{monospaced}}concat(){{monospaced}}, {{monospaced}}format(){{monospaced}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a SELECT statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Robert Bindar made changes - 2021-03-01 12:01

Description

Formatting more complex strings in a SELECT statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic C printf function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use behind the scenes the existing builtin formatting functions in MariaDB (e.g. date_format(), format()) and even future formatting functions (e.g. MySQL's format_bytes(), format_pico_time()), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Sergei Golubchik made changes - 2021-03-01 13:38

Description

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Sergei Golubchik made changes - 2021-03-01 13:39

Description

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
# Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
-- Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Sergei Golubchik made changes - 2021-03-01 13:41

Description

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}

Also this one has the advantage of being able to pass formatting options within the curly braces:
{code:sql}
-- Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be Python-ish, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}
In that syntax one passes formatting options within the curly braces:
{code:sql}
-- Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Robert Bindar made changes - 2021-03-02 09:28

Fix Version/s		10.7 [ 24805 ]
Fix Version/s	10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-04-09 11:12

Description

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be Python-ish, which would leverage the fact that the server already know the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}
In that syntax one passes formatting options within the curly braces:
{code:sql}
-- Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Formatting more complex strings in a {{SELECT}} statement can get awkward when there are many {{concat()}}, {{format()}}, etc calls involved.
It would be very cool and helpful to have a function that takes an input string and a formatting specification and returns string formatted using the rules the user passed in the specification.

A great example for such a function is the classic {{C printf}} function, which, in this context, would look something like:

{code:sql}SELECT printf('%s %s, %s', first_name, last_name, job_title) from employees;{code}

But it doesn't necessarily need to look this way, an alternative syntax could be Python-ish, which would leverage the fact that the server already knows the datatype of each field used in the formatting scheme:

{code:sql}SELECT sformat('arg1: {}, arg2: {}', col1, col2) from table;{code}
In that syntax one passes formatting options within the curly braces:
{code:sql}
-- Print 'arg1: col1, arg2: col2' where col1 from table is of datetime type and should be printed as: 'Sunday November 2021'
SELECT sformat('arg1: {%W %M %Y}, arg2: {}', col1, col2) from table;
{code}

Ideally, this new function should use, behind the scenes, the existing builtin formatting functions in MariaDB (e.g. {{date_format()}}, {{format()}}) and even future formatting functions (e.g. MySQL's {{format_bytes()}}, {{format_pico_time()}}), so the syntax has to be designed in a smart way to accommodate easily future additions.

Vicențiu Ciorbaru made changes - 2021-05-25 07:54

Assignee

Robert Bindar [ robertbindar ]

Vicențiu Ciorbaru [ cvicentiu ]

Sergei Golubchik made changes - 2021-06-14 15:03

Remote Link

This issue links to "PostgreSQL (Web Link)" [ 31357 ]

Sergei Golubchik made changes - 2021-06-14 15:05

Remote Link

This issue links to "Python 3 (Web Link)" [ 31358 ]

Sergei Golubchik made changes - 2021-06-14 15:06

Remote Link

This issue links to "Rust (Web Link)" [ 31359 ]

Sergei Golubchik made changes - 2021-06-14 15:07

Remote Link

This issue links to "C printf (Web Link)" [ 31360 ]

Sergei Golubchik made changes - 2021-06-29 07:45

Remote Link

This issue links to "{fmt} — implementation of C++20 std::format (Web Link)" [ 31408 ]

Sergei Golubchik made changes - 2021-08-08 15:21

Link

This issue blocks MDEV-19784 [ MDEV-19784 ]

Sergei Golubchik made changes - 2021-09-04 12:40

Priority

Minor [ 4 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2021-09-04 17:00

Assignee

Vicențiu Ciorbaru [ cvicentiu ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2021-09-04 17:00

Status

Open [ 1 ]

Confirmed [ 10101 ]

Sergei Golubchik made changes - 2021-09-04 17:00

Status

Confirmed [ 10101 ]

In Review [ 10002 ]

Sergei Golubchik made changes - 2021-09-05 19:48

Link

This issue blocks TODO-3121 [ TODO-3121 ]

Sergei Golubchik made changes - 2021-09-05 19:50

Assignee	Sergei Golubchik [ serg ]	Elena Stepanova [ elenst ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2021-09-05 19:50

Link

This issue relates to TODO-3118 [ TODO-3118 ]

Roel Van de Paar made changes - 2021-09-18 03:24

Assignee

Elena Stepanova [ elenst ]

Roel Van de Paar [ roel ]

Roel Van de Paar made changes - 2021-09-18 04:30

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Roel Van de Paar made changes - 2021-09-18 07:46

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Roel Van de Paar made changes - 2021-09-18 07:47

Assignee

Roel Van de Paar [ roel ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2021-09-18 10:46

Assignee

Sergei Golubchik [ serg ]

Roel Van de Paar [ roel ]

Roel Van de Paar made changes - 2021-09-20 07:02

Link

This issue causes ~~MDEV-26646~~ [ ~~MDEV-26646~~ ]

Roel Van de Paar made changes - 2021-09-20 07:56

Link

This issue relates to MDEV-26648 [ MDEV-26648 ]

Roel Van de Paar made changes - 2021-09-20 08:08

Link

This issue relates to MDEV-26649 [ MDEV-26649 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue causes MDEV-26648 [ MDEV-26648 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue relates to MDEV-26648 [ MDEV-26648 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue causes MDEV-26649 [ MDEV-26649 ]

Sergei Golubchik made changes - 2021-09-20 18:45

Link

This issue relates to MDEV-26649 [ MDEV-26649 ]

Sergei Golubchik made changes - 2021-09-20 18:47

Link

This issue causes MDEV-26653 [ MDEV-26653 ]

Roel Van de Paar made changes - 2021-09-26 20:00

Link

This issue causes ~~MDEV-26691~~ [ ~~MDEV-26691~~ ]

Roel Van de Paar made changes - 2021-09-30 00:31

Assignee

Roel Van de Paar [ roel ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2021-10-12 11:51

Fix Version/s		10.7.1 [ 26120 ]
Fix Version/s	10.7 [ 24805 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:24

Workflow

MariaDB v3 [ 119616 ]

MariaDB v4 [ 134400 ]

Ralf Gebhardt made changes - 2023-03-21 11:00

Labels

gsoc21

Preview_10.7 gsoc21

Elena Stepanova made changes - 2023-04-07 18:33

Link

This issue relates to MDEV-31024 [ MDEV-31024 ]

MariaDB Server

Custom formatting of strings in MariaDB queries

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration