如何修剪URL以仅具有路径 [英] How to trim a URL to have only path

问题描述

大家好

我需要帮助..真的！我正在运行SQLserver 2016.



我有一个包含很多og URL的表格我想要 SELECT 和修剪到另一个只有路径的colume。



columne名为 REDIRTO ，表名为 EVENTLOG 和值可能如下：

Hi guys
I need help.. really! Im running SQLserver 2016.

I have a table containing a lot og URLs which i would like to SELECT and trim to another colume having only the path.

The columne is called REDIRTO and the table is called EVENTLOG and values could be like:

mydomain.com/ordering
mydomain.com/shop/cart?23456
mydomain.com/profilepage/126378
us.mydomain.com/extranet
uk.mydomain.com/ordering

我想要的是一个具有域后第一个值的colume：

What i would like is a colume with the first value after the domain:

ordering
shop
profilepage
extranet
ordering

我试过用LEN位它是没有真正的工作。基本上我希望将字符串从左边修剪到第一个/到下一个/



我尝试过的事情：

I have tried to use LEN bit it is not really working. Basically i want the string to be trimmed from left to the first / and to the next /

What I have tried:

Select EVENT_ID, REDIRTO, Right([REDIRTO],LEN([REDIRTO])-CharIndex('/',[REDIRTO])) as PATH
From EVENTLOG

推荐答案

在使用SQL Server 2016时，您可以使用 STRING_SPLIT [ ^ ]用于分隔'/'字符上文本部分的功能，例如

As you are using SQL Server 2016 you can use the STRING_SPLIT[^] function to separate the parts of the text on the '/' characters e.g.

DECLARE @sample varchar(255) = 'mydomain.com/shop/cart?23456'
declare @temp table (id int identity(1,1), part varchar(255))
insert into @temp SELECT value FROM STRING_SPLIT(@sample, '/')
select part from @Temp where id = 2

如果您不能使用STRING_SPLIT，那么还有其他UDF用于拆分字符串的示例，例如如何在SQL Server中按分隔字符串拆分字符串.............. - SQLServerCentral [ ^ ]

或者，您可以将SUB_STRING与CHARINDEX一起使用......虽然它有点难看......

If you can't use STRING_SPLIT then there are other examples of UDFs for splitting strings e.g. How to Split a string by delimited char in SQL Server.............. - SQLServerCentral[^]
Alternatively, you can use SUB_STRING with CHARINDEX ...it's a bit ugly though...

DECLARE @sample varchar(255) = 'mydomain.com/shop/cart?23456'
SELECT LEFT(SUBSTRING(@sample, CHARINDEX('/',@sample) + 1, LEN(@sample)), CHARINDEX('/', SUBSTRING(@sample, CHARINDEX('/',@sample) + 1, LEN(@sample)))-1)

我更喜欢使用常用表格表达式 [ ^ ]。请参阅：

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

有关CTE的更多信息，请参阅：

WITH common_table_expression（Transact-SQL）| Microsoft Docs [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

这篇关于如何修剪URL以仅具有路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！