Combining the result of an ordered String_Split in a variable

Question

In the SqlServer database that I am using, the database name is similar to StackExchange.Audio.Meta or StackExchange.Audio or StackOverflow . By sheer luck, this is also the URL of the website. I only need to break it into points and cancel it: meta.audio.stackexchange . Add http:// and .com , and you're done. Obviously, StackOverflow does not need to do the opposite.

Using SqlServer 2016 string_split function I can easily split and rearrange the result:

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

It gives me

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

Since I need to have a url in a variable, I was hoping to combine 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 returns me the last value, StackExchange . I already noticed warnings for this answer that this trick only works for certain execution plans, as described here .

The problem is apparently caused by the order by clause. Without this, I get all the values, but then in the wrong order. I tried to add the ltrim and rtrim , as suggested in a Microsoft article, as well as a subquery, but so far with no luck.

Is there any way I can push Query Engine Sql Server 2016 to associate an ordered result with this string_split in a variable?

I know that I can use for XML or even a simple cursor to get the result I need, but I do not want to abandon this elegant solution for now.

When I run this in the stack data explorer, I cannot use the functions, because we lack permission to create them. I can perform Stored Procedures, but I was hoping I could evade them.

I prepared SEDE Query for experimentation. Database names that are expected, either without dots, aka StackOverflow , with 1 dot: StackOverflow.Meta or two dots, `StackExchange.Audio.Meta, a complete list of databases is here

Answer 1

I think that you complicate the situation too much. You can use PARSENAME :

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

Answer 2

That is why I have a presentation sequence (PS) in my split function. People often scoff at using UDF for such items, but this is usually a one-time punch to make out something for later consumption.

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

Returns

 Key_PS Key_Value 1 meta 2 audio 3 stackexchange 

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

Perhaps a less elegant solution, but it takes only a few lines and works with any number of points.

 ;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 tvvalue('.[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 any way by which I can push the Sql Server 2016 query engine to concatenate the ordered result from this string_split into a variable?

You can simply use CONCAT :

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

The reverse is performed using the order of parameters before CONCAT . Here is 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 leaves a trailing separator. I'm sure you could add some logic or COALESCE to prevent this from happening.

Also note that vNext will add STRING_AGG .

Answer 5

To answer the "X" of this XY problem and refer to the HTTPS switch (especially for Meta sites) and other site name changes, I wrote the following SEDE request , which displays all the site names in the format used in the list of network sites .

 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 '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