将有序 String_Split 的结果连接到变量中 [英] Concatenate the result of an ordered String_Split in a variable

问题描述

在我使用的 SqlServer 数据库中，数据库名称类似于 StackExchange.Audio.Meta 或 StackExchange.Audio 或 StackOverflow .幸运的是，这也是网站的 url.我只需要在点上拆分它并反转它:meta.audio.stackexchange.添加 http:// 和 .com 就完成了.显然 Stackoverflow 不需要任何逆向.

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.

使用 SqlServer 2016 string_split 函数我可以轻松拆分和对其结果重新排序:

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

这给了我

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

因为我需要将 url 放在一个变量中，所以我希望使用这个 answer 将它连接起来，所以我的尝试看起来像这个:

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

然而，这只会返回最后一个值，StackExchange.我已经注意到该答案上的警告，即此技巧仅适用于某些执行计划，如此处所述.

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.

问题似乎是由 order by 子句引起的.没有它，我会得到所有值，但顺序错误.我尝试按照 Microsoft 文章中的建议添加 ltrim 和 rtrim 函数以及子查询，但到目前为止还没有运气.

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.

有没有办法让 Sql Server 2016 查询引擎将 string_split 的有序结果连接到一个变量中?

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?

我知道我可以使用 for XML 甚至一个普通的游标来获得我需要的结果，但我还不想放弃这个优雅的解决方案.

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.

当我在 Stack Exchange Data Explorer 上运行它时，我无法使用函数，因为我们没有创建这些函数的权限.我可以做存储过程，但我希望我能避开那些.

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.

我准备了一个 SEDE 查询来进行试验.期望的数据库名称要么不带点，又名 StackOverflow，带 1 个点:StackOverflow.Meta 或 2 个点，`StackExchange.Audio.Meta，数据库的完整列表这里

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

推荐答案

我认为你把事情复杂化了.你可以使用 PARSENAME:

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'

这篇关于将有序 String_Split 的结果连接到变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！