How to strip Querystring from a field in T-SQL?

Question

I have a SQL table full of logs that shows response time from my pages. For example:

/MyPage1.aspx
/MyPage2.aspx
/MyPage3.aspx?index=4
/MyPage3.aspx?index=5
/MyPage3.aspx?index=7

However, I want to create a view that shows me the average load time for each page. To do this, I don't want the query strings included since I just want to group the pages by their URL before the question mark (so /MyPage3.aspx only shows up once).

What string functions are appropriate here to strip out the query string?

Answer 1

The tricky part here is.... the data doesn't always include a ? mark. You can find the position of the ? using the charindex, but to select the data to the left of it, you need to use CharIndex - 1. If the ? isn't found, CharIndex returns 0, and Left(data, 0-1) results in an error.

The simple solution is to make sure there is always something for the CharIndex to find. Like this:

Select Left(URL, CharIndex('?', URL + '?')-1)
From   @Temp

Notice that I do CharIndex of URL + '?'. If the data does not contain a question mark, the charindex function will return a value 1 greater than the length of the string which works nicely for the left function.