I'd prefer to use
Common Table Expressions[
^]. See:
DECLARE @tmp TABLE(uriaddress varchar(255))
INSERT INTO @tmp (uriaddress)
VALUES('mydomain.com/ordering'),
('mydomain.com/shop/cart?23456'),
('mydomain.com/profilepage/126378'),
('us.mydomain.com/extranet'),
('uk.mydomain.com/ordering')
;WITH CTE AS
(
SELECT 1 AS PartNo, LEFT(uriaddress, CHARINDEX('/', uriaddress)-1) AS UriPart, RIGHT(uriaddress, LEN(uriaddress) - CHARINDEX('/', uriaddress)) AS Remainder
FROM @tmp
WHERE CHARINDEX('/', uriaddress)>0
UNION ALL
SELECT PartNo + 1 AS PartNo, LEFT(Remainder, CHARINDEX('/', Remainder)-1) AS UriPart, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('/', Remainder)) AS Remainder
FROM CTE
WHERE CHARINDEX('/', Remainder)>0
UNION ALL
SELECT PartNo + 1 AS PartNo, Remainder AS UriPart, NULL AS Remainder
FROM CTE
WHERE CHARINDEX('/', Remainder)=0
)
SELECT * --UriPart
FROM CTE
WHERE PartNo =2
Result:
PartNo UriPart Remainder
2 ordering NULL
2 extranet NULL
2 profilepage 126378
2 shop cart?23456
2 ordering NULL
For further information about CTE, please see:
WITH common_table_expression (Transact-SQL) | Microsoft Docs[
^]
Recursive Queries Using Common Table Expressions[
^]