CODEWITHSUNDEEP
sqlsql-server
Vignesh Subramanian
Vignesh Subramanian

Reputation: 7289

SQL Server split string and access different parts of it

I need to update the url stored in a column.

My columns have values like this:

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

I want to update this URL to:

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

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 -

Is there an easier way to do this?

In C# we can do something like this:

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?

Upvotes: 3

Views: 2163

Answers (4)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

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

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

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

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

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/)

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

Upvotes: 1

Eralper
Eralper

Reputation: 6612

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

Here is the script to change the order of last part in your 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 
    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

enter image description here

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

;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

Upvotes: 2

jptr
jptr

Reputation: 188

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

Upvotes: 2

Related Questions