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'

Answer 2

This is exactly why I have the Presentation Sequence (PS) in my split function. People often scoff at using a UDF for such items, but it is generally a one-time hit to parse something for later consumption.

Select * from [dbo].[udf-Str-Parse]('meta.audio.stackexchange','.')

Returns

Key_PS  Key_Value
1       meta
2       audio
3       stackexchange

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('meta.audio.stackexchange','.')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1) NOT NULL , Key_Value varchar(max))

As

Begin
   Declare @intPos int,@SubStr varchar(max)
   Set @IntPos = CharIndex(@delimeter, @String)
   Set @String = Replace(@String,@delimeter+@delimeter,@delimeter)
   While @IntPos > 0
      Begin
         Set @SubStr = Substring(@String, 0, @IntPos)
         Insert into @ReturnTable (Key_Value) values (@SubStr)
         Set @String = Replace(@String, @SubStr + @delimeter, '')
         Set @IntPos = CharIndex(@delimeter, @String)
      End
   Insert into @ReturnTable (Key_Value) values (@String)
   Return 
End

Answer 3

Probably less elegant solution but it takes only a few lines and works with any number of dots.

;with cte as (--build xml
  select 1 num, cast('<str><s>'+replace(db_name(),'.','</s><s>')+'</s></str>' as xml) str
)
,x as (--make table from xml
    select row_number() over(order by num) rn, --add numbers to sort later
       t.v.value('.[1]','varchar(50)') s
    from cte cross apply cte.str.nodes('str/s') t(v)
)
--combine into string
select STUFF((SELECT '.' + s AS [text()]
            FROM x
            order by rn desc --in reverse order
            FOR XML PATH('')
            ), 1, 1, '' ) name

Answer 4

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?

You can just use CONCAT:

DECLARE @URL NVARCHAR(MAX)
SELECT @URL = CONCAT(value, '.', @URL) FROM STRING_SPLIT(DB_NAME(), '.')
SET @URL = CONCAT('http://', LOWER(@URL), 'com');

The reversal is accomplished by the order of parameters to CONCAT. Here's an example.

It changes StackExchange.Garage.Meta to http://meta.garage.stackexchange.com.

This can be used to split and reverse strings in general, but note that it does leave a trailing delimiter. I'm sure you could add some logic or a COALESCE in there to make that not happen.

Also note that vNext will be adding STRING_AGG.

Answer 5

To answer the 'X' of this XY problem, and to address the HTTPS switch (especially for Meta sites) and some other site name changes, I've written the following SEDE query which outputs all site names in the format used on the network site list.

SELECT name,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', name) > 0,
    IIF(PATINDEX('%.Meta', name) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', name) > 0,
      IIF(PATINDEX('%.Meta', name) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', name) > 0,
          CASE SUBSTRING(name, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Health' THEN 'medicalsciences'
          WHEN 'Health.Meta' THEN 'medicalsciences.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(name, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', name) > 0,
            CASE SUBSTRING(name, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(name, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', name) > 0,
              'meta.' + SUBSTRING(name, 0, PATINDEX('%.Meta', name)) + '.com',
              name + '.com'
            )
          )
        )
      )
    ) + '/'
  )
  FROM sys.databases WHERE database_id > 5