1

I am trying to extract an ID from a URL and running into some issues. The URL's will look something like this:

http://www.website.com/news/view.aspx?id=95

http://www.website.com/news/view.aspx?id=20&ReturnURL=%2fnews%2fview.aspx%3fid%3d20

I am trying to return back the number following "?id=" and nothing after the number. I will then convert it to an INT in reference to another table. Any suggestions as how to do this properly?

Community
  • 1
  • 1
Justin Baker
  • 15
  • 1
  • 3

4 Answers4

2

Use charindex to find the position of ?id and stuff to remove the characters that is before ?id. Then you use left to return the characters to the next &

declare @T table
(
  URL varchar(100)
);

insert into @T values
('http://www.website.com/news/view.aspx?id=95'),
('http://www.website.com/news/view.aspx?id=20&ReturnURL=%2fnews%2fview.aspx%3fid%3d20');


select left(T2.URL, charindex('&', T2.URL) - 1) as ID
from @T as T
  cross apply (select stuff(T.URL, 1, charindex('?id', T.URL) + 3, '')+'&') as T2(URL);
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • This appears to be working for me. I have modified it a bit to fit my solution, but the results appear to be what I was looking for. Thanks! – Justin Baker Feb 11 '14 at 15:55
  • it won't work for urls like ```http://www.website.com/news/view.aspx?a=1&id=95``` – kord Oct 15 '19 at 16:34
1

Here is an option that you can use when you want to find the value of any parameter value within a URL, it also supports parsing text values that contain a URL

DECLARE @Param varchar(50) = 'event'
DECLARE @Data varchar(8000) = 'User Logged into  https://my.website.org/default.aspx?id=3066&event=49de&event=true from ip'

DECLARE @ParamIndex int = (select PatIndex('%'+@param+'=%', @Data)+LEN(@param)+1)
-- @ParamValueSubstring chops off everthing before the first instance of the parameter value
DECLARE @ParamValueSubstring varchar(8000) = SubString(@Data, @ParamIndex, 8000)

SELECT @ParamValueSubstring as ParamSubstring

DECLARE @SpaceIndex int = (SELECT CHARINDEX(' ', @ParamValueSubstring))-1
DECLARE @AmpIndex int = (SELECT CHARINDEX('&', @ParamValueSubstring))-1
DECLARE @valueLength int = -1

-- find first instance of ' ' or & after parameter value
IF @SpaceIndex = -1
  SET @valueLength = @AmpIndex
ELSE IF @AmpIndex = -1
  SET @valueLength = @SpaceIndex
ELSE IF @SpaceIndex < @AmpIndex 
  SET @valueLength = @SpaceIndex
ELSE  
  SET @valueLength = @AmpIndex 

IF(@valueLength = -1) -- check to see if there was no space or '&' following the parameter value
BEGIN
   SET @valueLength = 8000
END

select Left(@ParamValueSubstring, @valueLength) as ParamValue

-- approach similar to idea function found here http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server/
tviscon2
  • 108
  • 7
0

I'm not totally clear on what you're asking. Are you asking how to get the value of id from the url when you are in the asp.net application? Then in the code behind you can

In c#

string id = Request.QueryString["id"]; // returns null if id not found

Reference

From this SO question Try this for integers:

int id;
if (!int.TryParse(Request.QueryString["id"], out id))
{
  // error case
}
Community
  • 1
  • 1
kschieck
  • 1,407
  • 2
  • 16
  • 29
  • That doesn't really make sense to me. Someone added the tag `asp.net` which leads me to believe he wants to get the value from the current url. It's not very clear. – kschieck Feb 11 '14 at 15:45
  • I noticed that someone suggested the tag so I removed it, definitely not ASP.NET. – Justin Baker Feb 11 '14 at 15:51
0

You could do it in an SQL function, like this:

declare @URL varchar(100)

--set @URL = 'http://www.website.com/news/view.aspx?id=95'
set @URL = 'http://www.website.com/news/view.aspx?id=20&ReturnURL=%2fnews%2fview.aspx%3fid%3d20'

Set @URL = CASE charindex('&',@URL) 
    WHEN 0 then @URL else substring(@url,1,charindex('&',@URL)-1) end

select @URL,SUBSTRING(@URL,CHARINDEX('?id=',@URL)+4,99)

Both examples are in there, comment either one to see result

BWS
  • 3,786
  • 18
  • 25