CODEWITHSUNDEEP
sql-serverstringsql-server-2008
Phil Sandler
Phil Sandler

Reputation: 28016

SQL Server Extracting Substring Between Two Characters

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

Given a string like the following (a 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 ("?").

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

Upvotes: 1

Views: 8213

Answers (3)

Rafi
Rafi

Reputation: 2484

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.

Upvotes: -1

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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

Upvotes: 3

Francisco Soto
Francisco Soto

Reputation: 10392

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

Upvotes: 0

Related Questions