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

I have a SQL table full of logs that shows response time from my pag开发者_开发问答es. 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?

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.

SELECT
    CASE
        WHEN CHARINDEX('?', url) > 0 THEN SUBSTRING(Url, 1, CHARINDEX('?', url) - 1)
        ELSE Url
    End As PageOnly
FROM
    TableName

This will give you all the unique URLs, and the number of times each was logged.

select UrlOnly, COUNT(*) as CountTimes
from
(
select
    case
    when URL like '%?%' then STUFF(URL, charindex('?',URL), LEN(URL), '')
    else URL
    end as UrlOnly
from tbl
) X
group by UrlOnly

You will want to use the SUBSTRING in conjunction with the CHARINDEX functions, so your SQL query will look something like

SELECT SUBSTRING([PageColName], 0, CHARINDEX('?', [PageColName])) AS 'Page', 
       LoadTime 
  FROM [TableName]

Replace [PageColName] with the name of your column that has the pages, as well as [TableName] with the name of the table you're querying from and you should be set.

Something like this

LEFT(fieldName, CHARINDEX('.aspx', fieldName) + 5)

You don't need the IF statement since your example every URL has an ASPX extension.