How to get a id from a url using SQL QUERY. The ID changes dynamically. the database is Sql server 2008

Question

I have url like:

http://mysites.xyz.com/_layouts/ng/ActivityStream.aspx/id/2624D92223261D370D7287C9E83CAEEA/Activity%20Stream%20Post.

I need to get the 2624D92223261D370D7287C9E83CAEEA. But not able to do so. my current query is

 SUBSTRING(@URL2,LEN('http://mysites.xyz.com/_layouts/ng/ActivityStream.aspx/id/'),LEN(@URL2)-LEN('http://mysites.xyz.com/_layouts/ng/ActivityStream.aspx/id/')- CHARINDEX('/',REVERSE(@URL2))))

Please suggest.

What SQL type are you using? (Which database) eg. PSQL (PostgreSQL), MySQL, T-SQL (MS SQL Server), etc. — LuigiEdlCarno
– LuigiEdlCarno, Commented Jun 4, 2015 at 12:34
You could do it easily using any server side language like php or ruby and then pass the result to your SQL query. — niranjan94
– niranjan94, Commented Jun 4, 2015 at 12:37
Is it always /id/<some alpha-numerics>/Activity/..., or can it change? — Zohar Peled
– Zohar Peled, Commented Jun 4, 2015 at 12:37
By just going through the functions, I guessed its SQL Server! — Rahul Tripathi
– Rahul Tripathi, Commented Jun 4, 2015 at 12:38
yes zohar, the structure is always the same but the length of <some alpha-numerics> keeps on changing. — Deepika
– Deepika, Commented Jun 4, 2015 at 12:41

Zohar Peled · Accepted Answer · 2015-06-04 13:57:19Z

4

Try this:

SELECT SUBSTRING(
       @URL2, 
       CHARINDEX('/id/', @URL2)+4, 
       CHARINDEX('/', @URL2, CHARINDEX('/id/', @URL2)+5)
        - (CHARINDEX('/id/', @URL2)+4))

Note: This assumes that the id is always followed by at least one more slash.

Breakdown:
The first argument of the substring is the string that contains the full expression.
The second one is the first index after /id/. The third one is the desired length - calculated by the first index of / after /id/ - the first index after /id/.

update

To cope with strings that does not contain a slash after the id value, use case:

SELECT SUBSTRING(
       @URL, 
       CHARINDEX('/id/', @URL)+4, 
       CASE WHEN CHARINDEX('/', @URL, CHARINDEX('/id/', @URL)+5) > 0 THEN
       CHARINDEX('/', @URL, CHARINDEX('/id/', @URL)+5)
        - (CHARINDEX('/id/', @URL)+4)
       ELSE
          LEN(@URL)
       END
       )

edited Jun 4, 2015 at 13:57

answered Jun 4, 2015 at 12:41

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zohar Peled Over a year ago

You get this error when a the url does not contain a slash after the id value. This is why I've asked you this question in the first place. I'll edit my question in a few minutes to cope with this new information.

Giorgi Nakeuri · Accepted Answer · 2015-06-04 12:59:35Z

0

If the structure is always the same then you can try:

SELECT REPLACE(RIGHT(@s, LEN(@s) - 58), '/Activity%20Stream%20Post', '')

or:

SELECT REPLACE(REPLACE(@s,'/Activity%20Stream%20Post', ''), 'http://mysites.xyz.com/_layouts/ng/ActivityStream.aspx/id/', '')

answered Jun 4, 2015 at 12:59

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Collectives™ on Stack Overflow

How to get a id from a url using SQL QUERY. The ID changes dynamically. the database is Sql server 2008

2 Answers 2

update

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

update

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related