How to get a part of string from a url

Question

0.00/5 (No votes)

See more:

I have a URL, for example http://www.codeproject.com/Questions/ask.aspx

I only need 'Questions' from the above URL. Please help.

Posted 20-Aug-13 2:43am

Shehzaada Salim

Add a Solution

Comments

Joezer BH 20-Aug-13 9:46am

Note the "SQL" tag on the question.

Shehzaada Salim 21-Aug-13 3:47am

I certainly mean SQL. :)

2 solutions

Solution 2

Solution1 by Mike Meinz is very good. I would suggest to use Common Table Expressions[^].

SQL

DECLARE @tmp TABLE (url VARCHAR(300))

INSERT INTO @tmp (url)
SELECT 'http://www.codeproject.com/Questions/ask.aspx'
UNION ALL SELECT 'http://www.codeproject.com/script/Answers/List.aspx?tab=unanswered'
UNION ALL SELECT 'http://www.codeproject.com/script/Answers/List.aspx?tab=active'
UNION ALL SELECT 'http://www.codeproject.com/Forums/1580997/Application-Lifecycle.aspx'
UNION ALL SELECT 'http://www.codeproject.com/Questions/640139/How-to-get-a-part-of-string-from-a-url'


;WITH UrlParts AS
(
	--initiall part
	SELECT url, LEFT(url, CHARINDEX('/',url)-1) AS Part, RIGHT(url, LEN(url) - CHARINDEX('/',url)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX('/',url)>0
        --recursion part
	UNION ALL
	SELECT url, LEFT(Remainder, CHARINDEX('/',Remainder)-1) AS Part, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('/',Remainder)) AS Remainder
	FROM UrlParts
	WHERE CHARINDEX('/',Remainder)>0
	UNION ALL
	SELECT url, Remainder AS Part, NULL AS Remainder
	FROM UrlParts
	WHERE CHARINDEX('/',Remainder)=0
)
SELECT *
FROM UrlParts
WHERE Part = 'Questions'

Result:

http://www.codeproject.com/Questions/640139/How-to-get-a-part-of-string-from-a-url  Questions   640139/How-to-get-a-part-of-string-from-a-url
http://www.codeproject.com/Questions/ask.aspx   Questions   ask.aspx

Posted 20-Aug-13 10:27am

Maciej Los

Comments

Joezer BH 21-Aug-13 3:53am

5ed too!
Always good SQL answers from Mr. Los!

Maciej Los 21-Aug-13 3:56am

Thank you ;)

Mike Meinz 21-Aug-13 7:12am

Very nice! Especially recursion!!!

Maciej Los 21-Aug-13 7:27am

Thank you, Mike ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-08-20T02:51:00

You tagged your question with SQL so my answer shows you the tools to use to get what you want using Microsoft Transact SQL.

Use the Microsoft Transact SQL String functions: CHARINDEX and SUBSTRING.
* CHARINDEX to locate the "/" characters.
* SUBSTRING to extract the directory name using the index values returned by CHARINDEX.

This example contains no error checking. When used in an application, there should be error checking.

SQL

declare @URL varchar(255);
declare @idx1 int;
declare @str1 varchar(255);

set @URL='http://www.codeproject.com/Questions/ask.aspx';
set @idx1=CHARINDEX('//',@URL);
set @str1=SUBSTRING(@URL,@IDX1+2,255);
set @idx1=CHARINDEX('/',@str1);
set @str1=SUBSTRING(@str1,@IDX1+1,255);
set @idx1=CHARINDEX('/',@str1);
set @str1=SUBSTRING(@str1,0,@idx1);
select @str1;

The above code could be used in a scalar-valued function that could be called from a SELECT statement. The following example creates a scalar-valued function. Note: In an application, there should be error checking code.

SQL

use MyDatabaseName
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION URLTopLevelFolder(@URL varchar(255))
RETURNS varchar(255)
AS
BEGIN
declare @idx1 int;
declare @str1 varchar(255);
set @idx1=CHARINDEX('//',@URL);
set @str1=SUBSTRING(@URL,@IDX1+2,255);
set @idx1=CHARINDEX('/',@str1);
set @str1=SUBSTRING(@str1,@IDX1+1,255);
set @idx1=CHARINDEX('/',@str1);
set @str1=SUBSTRING(@str1,0,@idx1);
RETURN @str1
END
GO

Example of use of the scalar-valued function

SQL

select dbo.URLTopLevelFolder('http://www.codeproject.com/Questions/ask.aspx')

It might provide better performance to do this type of character string handling/extraction in the application programming language rather than in the database server.