New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Non grouping version of LIST function [CORE4335] #4658
Comments
Modified by: Tim Kelly (m00bh000)description: I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-grouping version of LIST function, say STRINGLIST this would basically concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- => I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-grouping version of LIST function, say STRINGLIST this would basically concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- Currently I do this (which is almost equivalent) SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma! I suggest the any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. |
Modified by: Tim Kelly (m00bh000)description: I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-grouping version of LIST function, say STRINGLIST this would basically concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- Currently I do this (which is almost equivalent) SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma! I suggest the any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. => The aggregate LIST function is very useful however there is no non-aggregate version. I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-grouping version of LIST function, say STRINGLIST this would basically concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- Currently I do this (which is almost equivalent) SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma! I suggest the any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. |
Modified by: Tim Kelly (m00bh000)description: The aggregate LIST function is very useful however there is no non-aggregate version. I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-grouping version of LIST function, say STRINGLIST this would basically concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- Currently I do this (which is almost equivalent) SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma! I suggest the any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. => The aggregate LIST function is very useful however unfortunately currently there is no non-aggregate version. I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-aggregate version of the LIST function, say STRINGLIST. This would concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- Currently I do this (which is almost equivalent) SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma! I suggest that any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. An option to remove duplicates would be handy similar to the aggregate version. |
Modified by: Tim Kelly (m00bh000)description: The aggregate LIST function is very useful however unfortunately currently there is no non-aggregate version. I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-aggregate version of the LIST function, say STRINGLIST. This would concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- Currently I do this (which is almost equivalent) SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma! I suggest that any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. An option to remove duplicates would be handy similar to the aggregate version. => The aggregate LIST function is very useful however unfortunately currently there is no non-aggregate version. I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas. I propose a new non-aggregate version of the LIST function, say STRINGLIST. This would concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list). SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE: ---- SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE --- Example of real-world usage: SELECT STRINGLIST(ADDRESS_LINE1, ADDRESS_LINE2, CITY, ZIPCODE) FROM... Currently I do this (which is almost equivalent) SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma! I suggest that any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. An option to remove duplicates would be handy similar to the aggregate version. |
Commented by: @dyemanov Why not to create a user-defined function FORMAT_ADDRESS(LINE1, LINE2, CITY, ZIPCODE)? |
Commented by: Tim Kelly (m00bh000) The address was just an example, a general purpose function to comma separate concatenated expressions would be really helpful. Yes I could get round to writing a UDF but I still think this is useful enough to be built in. There are non-aggregate versions of the other aggregate functions eg MIN -> MINVALUE, MAX -> MAXVALUE, SUM -> +, It seems to me there should be one for LIST, oh and AVG too. |
Commented by: Tim Kelly (m00bh000) Maybe LISTVALUE & AVGVALUE would be more in keeping with the naming of the other functions. |
Submitted by: Tim Kelly (m00bh000)
The aggregate LIST function is very useful however unfortunately currently there is no non-aggregate version.
I frequently concatenate different fields together, but it would be useful to be able to do this separating the result with commas.
I propose a new non-aggregate version of the LIST function, say STRINGLIST. This would concatenate string versions of it's arguments but correctly place commas (so there is no comma at the end of the list).
SELECT STRINGLIST('A','B','C') FROM RDB$DATABASE:
----
STRINGLIST
A,B,C
----
SELECT STRINGLIST('A',NULL,'C') FROM RDB$DATABASE
---
STRINGLIST
A,C
---
Example of real-world usage:
SELECT STRINGLIST(ADDRESS_LINE1, ADDRESS_LINE2, CITY, ZIPCODE) FROM...
Currently I do this (which is almost equivalent)
SELECT TRIM(TRAILING ',' FROM COALESCE('A' || ',','') || COALESCE('B' || ',','') || COALESCE('C' || ',','')) FROM RDB$DATABASE
But ideally an empty list would return NULL instead of '', and also the above is slightly dangerous as if the string contains a comma!
I suggest that any such function has an optional argument to decide whether empty list should be null or '', and also the option to quote each element in the list. An option to remove duplicates would be handy similar to the aggregate version.
The text was updated successfully, but these errors were encountered: