Forum Stats

  • 3,824,841 Users
  • 2,260,429 Discussions
  • 7,896,325 Comments

Discussions

New function CONCAT_WS

Thorsten Kettner
Thorsten Kettner Member Posts: 42 Red Ribbon
edited Nov 26, 2020 12:51PM in Database Ideas - Ideas

PostgreSQL, MySQL and SQL Server have the function CONCAT_WS to concatenate strings with a separator. NULL strings are ignored. Admittedly, this is a function that is seldom needed, but in the rare cases were appropriate, this function can be very convienient. I suggest to introduce this function in Oracle, too.

Example:

SELECT CONCAT_WS(' ', title, first_name, last_name) AS name FROM persons;

NAME

---------------

Mrs. Jane Smith

Hank Miller

Mr. Jones

Thorsten KettnerSven W.Niels Heckeruser11970842
5 votes

Active · Last Updated

Comments

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,816 Red Diamond

    Doc reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver15

    It  is not useful for CSV formatting as nulls are 'skipped'. I do not see a real need for this function, and it begs the question why deal with data rendering issues on the server side, instead of the client side?

  • Thorsten Kettner
    Thorsten Kettner Member Posts: 42 Red Ribbon

    Doc reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver15

    It  is not useful for CSV formatting as nulls are 'skipped'. I do not see a real need for this function, and it begs the question why deal with data rendering issues on the server side, instead of the client side?

    No, for CSV it's not appropriate. As mentioned, this function is not often needed. Only once in a while, but then it's useful.

    The argument data rendering issues should not be dealt with on the server side could be used against all string functions, like SUBSTR, INSTR, TRIM, conversion functions like TO_CHAR, TO_NUMBER, etc.

    The other DBMS have this function, because it can be useful every now and then. So why not have it, too? It can't be a big deal to implement it. And if you don't want to use it, don't use it.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,816 Red Diamond

    No, for CSV it's not appropriate. As mentioned, this function is not often needed. Only once in a while, but then it's useful.

    The argument data rendering issues should not be dealt with on the server side could be used against all string functions, like SUBSTR, INSTR, TRIM, conversion functions like TO_CHAR, TO_NUMBER, etc.

    The other DBMS have this function, because it can be useful every now and then. So why not have it, too? It can't be a big deal to implement it. And if you don't want to use it, don't use it.

    There are differences between transformation functions (e.g. converting a Unix time integer into a date), and a pure user presentation function like CONCAT_WS().

    As for transformation functions being used for doing what should be done in the presentation layer, is not a problem with the function itself, but with the user decision to move portions of the presentation layer into server layer SQL.

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Apr 24, 2020 12:57PM

    ok why not.

    I assume that the number of possible strings could be quite long (ellipsis argument?).

    Similar to the coalesce function and some others.

    There could be useful scenarios, especially if one of the in between values is null.

    SELECT CONCAT_WS('', title, first_name, middle_name_inital, last_name) AS name FROM persons;

    Not many persons have a middle initial and/or a title. The new function would avoid the need to add some logic that eliminates double blanks.

    Thorsten Kettner