Concatenate the result of an ordered String_Split in a variable

Question

In a SqlServer database I use, the database name is something like StackExchange.Audio.Meta, or StackExchange.Audio or StackOverflow . By sheer luck this is also the url for a website. I only need split it on the dots and reverse it: meta.audio.stackexchange. Adding http:// and .com and I'm done. Obviously Stackoverflow doesn't need any reversing.

Using the SqlServer 2016 string_split function I can easy split and reorder its result:

select value
from string_split(db_name(),'.')
order by row_number() over( order by (select 1)) desc

This gives me

|  Value        |
-----------------
| Meta          |
| Audio         |
| StackExchange |

As I need to have the url in a variable I hoped to concatenate it using this answer so my attempt looks like this:

declare @revname nvarchar(150)
select @revname = coalesce(@revname +'.','')  + value
from string_split(db_name(),'.')
order by row_number() over( order by (select 1)) desc

However this only returns me the last value, StackExchange. I already noticed the warnings on that answer that this trick only works for certain execution plans as explained here.

The problem seems to be caused by the order by clause. Without that I get all values, but then in the wrong order. I tried to a add ltrimand rtrim function as suggested in the Microsoft article as well as a subquery but so far without luck.

Is there a way I can nudge the Sql Server 2016 Query Engine to concatenate the ordered result from that string_split in a variable?

I do know I can use for XML or even a plain cursor to get the result I need but I don't want to give up this elegant solution yet.

As I'm running this on the Stack Exchange Data Explorer I can't use functions, as we lack the permission to create those. I can do Stored procedures but I hoped I could evade those.

I prepared a SEDE Query to experiment with. The database names to expect are either without dots, aka StackOverflow, with 1 dot: StackOverflow.Meta or 2 dots, `StackExchange.Audio.Meta, the full list of databases is here

Answer 1

I think you are over-complicating things. You could use PARSENAME:

SELECT 'http://' + PARSENAME(db_name(),1) + 
       ISNULL('.' + PARSENAME(db_name(),2),'') + ISNULL('.'+PARSENAME(db_name(),3),'') 
       + '.com'