I was starting down the road of testing within group(), and I came across this unexpected behavior of string_agg (or maybe substring). In the following code, mType is a field containing things like 'HMA - etc.etc.etc.' or 'ACP - etc.etc.etc.' In the following context, the 2nd selected field fails to change the delimiter from a ',' to a '-'
select string_agg( substring( mType,1,3 ), ',' ) mType
,string_agg( substring( mType,1,3), '-' ) mType2
from ourDB..mTypeTable
where ref = '3944900'
Returns:
If I change the substring from 1,3 to 1,4, then things work.
select string_agg( substring( mType,1,3 ), ',' ) mType
,string_agg( substring( mType,1,4), '-' ) mType2
from ourDB..mTypeTable
where ref = '3944900'
Returns:
Why does TSQL fail to change the delimiter in the first context? Is this some kind of optimization moment or something where TSQL is reusing the last substring because it's pulling from the same field in the same table in the same select?
