SQL Server提取两个字符之间的子串

Looking for a good way to do the following string manipulation in SQL Server (2008):

寻找在SQL Server(2008)中执行以下字符串操作的好方法:

Given a string like the following (a URL):

给定如下字符串(URL):

/path/page.aspx?variable=value

I need to extract "page.aspx". So the rule would be to grab the text between the last instance of a forward slash ("/") and the first instance of a question mark ("?").

我需要提取“page.aspx”。因此,规则是在正斜杠的最后一个实例(“/”)和问号的第一个实例(“?”)之间获取文本。

It is important to note that there may be zero or more forward slashes and zero or more question marks in the string. So the following would be valid input:

重要的是要注意,字符串中可能有零个或多个正斜杠和零个或多个问号。所以以下是有效的输入:

/page.aspx?variable=value
page.aspx?variable=value
/path/page.aspx
page.aspx

3 个解决方案

#1

This should handle all cases, including missing slashes or question marks:

这应该处理所有情况,包括缺少斜线或问号:

DECLARE @TestData TABLE
(
    URL VARCHAR(500)
)

INSERT INTO @TestData(URL) VALUES ('/path/page.aspx?variable=value')
INSERT INTO @TestData(URL) VALUES ('/page.aspx?variable=value')
INSERT INTO @TestData(URL) VALUES ('page.aspx?variable=value')
INSERT INTO @TestData(URL) VALUES ('/path/page.aspx')
INSERT INTO @TestData(URL) VALUES ('page.aspx')


SELECT
    URL,
    SUBSTRING(URL, 
        ISNULL(2 + LEN(URL) - NULLIF(CHARINDEX('/', REVERSE(URL)), 0), 0), 
        CASE CHARINDEX('?', URL) WHEN 0 THEN LEN(URL) + 1 ELSE CHARINDEX('?', URL) END - 
        ISNULL(2 + LEN(URL) - NULLIF(CHARINDEX('/', REVERSE(URL)), 0), 0)) AS Page
FROM
    @TestData

#2

SELECT SUBSTRING(url, 
                 LEN - CHARINDEX(REVERSE(url), '/') - 1,
                 CHARINDEX(url, '?') - (LEN - CHARINDEX(REVERSE(url), '/'))
                )

This will probably work (or maybe with some minor changes I didn't test it).

这可能会起作用(或者可能有一些小的改变,我没有测试它)。

#3

I know this is old but here is a link to great Text Manipulation functions for Sql Server. http://bradsruminations.blogspot.com/2010/01/handy-string-functions.html You want the STREXTRACT() function.

我知道这是旧的,但这里是一个链接到Sql Server的伟大的文本操作功能。 http://bradsruminations.blogspot.com/2010/01/handy-string-functions.html你想要STREXTRACT()函数。

#1