SQL Server分割字符串并访问它的不同部分

I need to update the url stored in a column.

我需要更新列中存储的url。

My columns have values like this:

我的列有这样的值:

https://www.site.sharepoint.com/sites/test/AB-19-CALL

I want to update this URL to:

我想将此URL更新为:

https://www.site.sharepoint.com/sites/test/CALL-AB-19

To get the last part which is AB-19-CALL, I used the below query

为了获得最后一部分ab -19调用，我使用了下面的查询

SELECT 
    SUBSTRING(urlcompte, LEN(urlcompte) - CHARINDEX('/', REVERSE(urlcompte)) + 2, LEN(urlcompte)), 
    UrlCompte 
FROM 
    tblAccount

Now to split and reverse the AB-19-call I would again need to use the entire query above and then select substring using -

现在要拆分和反转ab -19调用，我还需要使用上面的整个查询，然后使用-选择子字符串

Is there an easier way to do this?

有更简单的方法吗?

In C# we can do something like this:

在c#中，我们可以这样做:

urlCompte.Split('-')[2] + urlCompte.Split('-')[0] + urlCompte.Split('-')[1]

Is there a way in SQL Server to split the column and access different parts of it?

SQL Server中是否有方法分割列并访问它的不同部分?

4 个解决方案

#1

This would work to split the string parts -

这将有助于分割字符串部分-

DECLARE @txt NVARCHAR(500)= 'https://www.site.sharepoint.com/sites/test/AB-19-CALL';

SELECT value
FROM STRING_SPLIT(REVERSE(SUBSTRING(REVERSE(@txt), 1, CHARINDEX('/', REVERSE(@txt))-1)), '-');(substring(reverse(@txt),1,charindex('/',reverse(@txt))-1)),'-')

#2

If you are so lucky to be using SQL Server 2017 you can use for string_agg function for concatenating splitted string pieced with SQL string_split function

如果您有幸使用SQL Server 2017，那么可以使用string_agg函数连接使用SQL string_split函数分段的字符串

Here is the script to change the order of last part in your url

这是修改url中最后一部分的顺序的脚本

declare @url varchar(100) = 'CALL-AB-19'

select 
    string_agg(value,'-') within group (order by rn desc) 
from (
    select 
        value, row_number() over (order by @url) as rn 
    from STRING_SPLIT( @url , '-' )
) t

If you want to apply this solution as a set based solution on your table rows with a single SELECT statement, you can execute following SQL

如果您想将此解决方案应用为基于表行、使用一个SELECT语句的集的解决方案，您可以执行以下SQL

select 
    id, string_agg(value,'-') within group (order by rn desc) 
from (
    select 
        id, value, row_number() over (partition by id order by url) as rn 
    from urlList
    cross apply STRING_SPLIT( url , '-' )
) t
group by id

I assume, in your table urlList, you have a PK field id

我假设，在您的表urlList中，您有一个PK字段id

If you don't use SQL Server 2016 for split function and SQL Server 2017 for string aggregate function, you can search the web for split string function samples. And using FOR XML Path to concatenate string parts, you can build a solution with the help of CTE expression as follows

如果分割函数不使用SQL Server 2016，字符串聚合函数不使用SQL Server 2017，可以在web上搜索分割字符串函数示例。使用FOR XML路径连接字符串部分，您可以使用CTE表达式构建一个解决方案，如下所示

;with cte as (
    select
        urlList.id,
        urlList.url,
        s.id sid,
        s.val
    from urlList
    cross apply dbo.split(url,'-' ) s
)
SELECT
    distinct
    id,
    STUFF(
        (
        SELECT
            '-' + u.val
        FROM cte as u
        where u.id = cte.id
        Order By sid desc
        FOR XML PATH('')
        ), 1, 1, ''
    ) As newurl
FROM cte

Please note that the referred split function returns an id named numeric field which shows the order of the splitted piece. So while concatenating I use an "order by" clause on the same field this time in descending order

请注意，引用的split函数返回一个名为numeric字段的id，该字段显示分割块的顺序。因此，在连接时，我在同一字段上使用“order by”子句，这次是按降序排列的

#3

If someone is using SQL server 2012 or older version then it can be possible by this way:

如果有人正在使用SQL server 2012或更老的版本，那么可以通过以下方式实现:

DECLARE @URL VARCHAR(100) = 'https://www.site.sharepoint.com/sites/test/AB-19-CALL'

SELECT TOP 1 dbo.[Reversedata](DATA, '-')
FROM (SELECT * FROM dbo.Splitter(@URL, '/') as t) as tt order by Id desc

In above code I have used two functions: 1) Splitter : To split the string (https://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html) 2) Reversedata: To reverse the data (http://picnicerror.net/development/sql-server/reverse-order-words-string-sql-server-2012-01-16/)

在上面的代码中，我使用了两个函数:1)Splitter:拆分字符串(https://ole.michelsen.dk/blog/split-string-using-transact -sql.html)

Splitter:

分束器:

CREATE FUNCTION [dbo].[Splitter]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

Reverse:

相反:

CREATE FUNCTION [dbo].[udf_ReverseSequenceOrder] (
@Input nvarchar(200)
,@Delimiter nvarchar(5)
)

RETURNS nvarchar(200)
AS

BEGIN

DECLARE @Output nvarchar(200)

WHILE LEN(@Input) > 0
BEGIN
IF CHARINDEX(@Delimiter, @Input) > 0
BEGIN
SET @Output = SUBSTRING(@Input,0,CHARINDEX(@Delimiter, @Input)) + @Delimiter + ISNULL(@Output,'')
SET @Input = SUBSTRING(@Input,CHARINDEX(@Delimiter, @Input)+1,LEN(@Input))
END
ELSE
BEGIN
SET @Output = @Input + @Delimiter + ISNULL(@Output,'')
SET @Input = ''
END
END

RETURN SUBSTRING(@Output,0,LEN(@Output))
END

#4

Well, this is the xml based solution. For SQL Server 2008 and above.

这是基于xml的解决方案。适用于SQL Server 2008及以上版本。

DECLARE @url VARCHAR(100) = 'AB-19-CALL'

SELECT MyUrl FROM 
(
    SELECT CAST('<Url><Part>' + REPLACE(@url,'-','</Part><Part>') + '</Part></Url>' AS XML) AS my_Xml 
) t1
CROSS APPLY 
(
     SELECT 
        my_Data.D.value('Part[3]','varchar(50)') + '-' + 
        my_Data.D.value('Part[1]','varchar(50)') + '-' + 
        my_Data.D.value('Part[2]','varchar(50)') AS MyUrl
     FROM t1.my_Xml.nodes('/Url') as my_Data(D)
) t2

Result

结果

MyUrl
----------
CALL-AB-19

#1