英文:
SQL to remove text from the left hand side of the fourth special character
问题
我无法找到正确的函数组合来删除第四个特殊字符“\”左侧的所有文本。
我有以下字符串...
pw:\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents_Demonstration\PROJ1_JAC\30 Project Data\A - Architecture\03 - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001
...我想删除第四个“\”左侧的所有内容，以便我只剩下
_Demonstration\PROJ1_JAC\30 Project Data\A - Architecture\03 - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001
我在MySQL中看到一个函数（SUBSTRING_INDEX（）），但在SQL Server中找不到相应的函数。
英文:
I can’t quite find the correct combination of function(s) to remove all text from the left hand side of the fourth special character ‘\’.
I have the following string…
pw:\\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents\_Demonstration\PROJ1_JAC Project Data\A - Architecture - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001
…and I’d like to remove everything to the left of the 4th ‘\’ such that I’m left with
_Demonstration\PROJ1_JAC Project Data\A - Architecture - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001
I can see a function in MySQL (SUBSTRING_INDEX()) but can’t see an equivalent in SQL Server.
答案1
得分: 1
一种解决方案是将您的分隔字符串视为json 数组，然后可以根据数组元素位置进行过滤和聚合：
with t as (
select string = 'pw:\\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents\_Demonstration\PROJ1_JAC Project Data\A - Architecture - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001'
)
select String_Agg(value, '\') within group(order by j.[key])
from t
cross apply OpenJson(Concat('[\"',replace(string, '\','\",'),'\"]'))j
where j.[key] >= 4;
英文:
One solution is to treat your delimited string as a json array, you can simply then filter on the array element position and aggregate:
with t as (
select string = 'pw:\\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents\_Demonstration\PROJ1_JAC Project Data\A - Architecture - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001'
)
select String_Agg(value, '\') within group(order by j.[key])
from t
cross apply OpenJson(Concat('["',replace(string, '\', '","'),'"]'))j
where j.[key] >= 4;
答案2
得分: 0
在2022年以后，您可以使用STRING_SPLIT和STRING_AGG函数，同时将STRING_SPLIT函数的结果限制为第5个分隔值及以后的部分：
DECLARE @YourString varchar(MAX) = 'pw:\\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents\_Demonstration\PROJ1_JAC Project Data\A - Architecture - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001'
SELECT STRING_AGG(SS.value,'\') WITHIN GROUP (ORDER BY SS.ordinal)
FROM STRING_SPLIT(@YourString,'\') SS
WHERE SS.ordinal >= 5;
英文:
On 2022+, you could just use STRING_SPLIT and STRING_AGG while limiting the results from STRING_SPLIT to the 5th delimited value onwards:
DECLARE @YourString varchar(MAX) = 'pw:\\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents\_Demonstration\PROJ1_JAC\30 Project Data\A - Architecture\03 - Documents\PROJ1_JAC-JAC-XX-00-D-A-0001'
SELECT STRING_AGG(SS.value,'\') WITHIN GROUP (ORDER BY SS.ordinal)
FROM STRING_SPLIT(@YourString,'\',1) SS
WHERE SS.ordinal >= 5;
答案3
得分: 0
使用T-SQL中的Substring_Index()函数，你可以编写如下代码：
declare @str varchar(MAX) =
'pw:\\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents\'
+'_Demonstration\PROJ1_JAC Project Data\A - Architecture '
+'- Documents\PROJ1_JAC-JAC-XX-00-D-A-0001'
select dbo.Substring_Index(@str, dbo.Substring_Index(@str, '\', 4) + '\', -1)
请注意，这是一个示例代码，具体的实现可能需要根据你的需求进行调整。
英文:
With this Substring_Index() in T-SQL you could write something like:
declare @str varchar(MAX) =
'pw:\\abcdef-uk-ldn-pw.acme.com:abcdef-uk-ldn-pw-02\Documents\'
+'_Demonstration\PROJ1_JAC Project Data\A - Architecture '
+'- Documents\PROJ1_JAC-JAC-XX-00-D-A-0001'
select dbo.Substring_Index(@str, dbo.Substring_Index(@str, '\', 4) + '\', -1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论