如何从网址获取字符串的一部分 [英] How to get a part of string from a url

问题描述

我有一个网址，例如 http://www.codeproject.com/Questions/ask.aspx





我只需要上述网址中的问题。请帮助。

解决方案

您使用SQL标记了您的问题所以我的答案显示了使用Microsoft Transact SQL获取所需内容的工具。



使用Microsoft Transact SQL String函数：CHARINDEX和SUBSTRING。

* CHARINDEX找到/字符。

* SUBSTRING使用CHARINDEX返回的索引值提取目录名。



此示例不包含错误检查。在应用程序中使用时，应该进行错误检查。

 声明  @ URL   varchar （ 255 ）; 
 声明  @ idx1   int  ; 
 声明  @ 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 ）; 
 选择  @ str1 ; 

上面的代码可用于标量值函数，可以从 SELECT 语句调用。以下示例创建标量值函数。注意：在应用程序中，应该有错误检查代码。

  use  MyDatabaseName 
  go  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  CREATE   FUNCTION  URLTopLevelFolder（ @ URL   varchar （ 255 ））
 退货  varchar （ 255 ）
  AS  
  BEGIN  
  d eclare   @ idx1   int ; 
 声明  @ 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  

使用标量值函数的示例

 选择 dbo.URLTopLevelFolder（'  http ：//www.codeproject.com/Questions/ask.aspx'）

它可能提供更好的性能这种类型的字符串处理/提取在应用程序编程语言中而不是在数据库服务器中。

Mike Meinz的Solution1非常好。我建议使用公用表格式 [ ^ ]。



< pre lang =SQL> DECLARE @ tmp TABLE （url VARCHAR （ 300 ））

INSERT INTO @ tmp （url）
< span class =code-keyword> SELECT ' http://www.codeproject.com/Questions /ask.aspx'
UNION ALL SELECT ' http://www.codeproject.com/script/Answer s / List.aspx？tab =未答复'
UNION 所有 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/如何得到一个字符串来自一个网址'


; WITH UrlParts AS
（
- initiall part
SELECT url， LEFT （url，CHARINDEX（ ' /'，url）-1） AS Part， RIGHT （url，LEN（url） - CHARINDEX（' /'，url）） AS 剩余
FROM < span class =code-sdkkeyword> @ tmp
WHERE CHARINDEX（'' /'，url）> 0
- 递归部分
UNION ALL
SELECT url， LEFT （剩余，CHARINDEX（' <跨度lass =code-string> /'，剩余）-1） AS 部分， RIGHT （剩余，LEN（剩余） - CHARINDEX（' /'，剩余）） AS 剩余
FROM UrlParts
WHERE CHARINDEX（' /'，Remainder）> 0
UNION ALL
SELECT url，剩余 AS 部分， NULL AS 剩余
< span class =code-keyword> FROM UrlParts
WHERE CHARINDEX（'' /'，剩余）= 0
）
SELE CT *
FROM UrlParts
WHERE Part = ' 问题'

结果：

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

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


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

解决方案

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.

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.

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

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.

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

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

这篇关于如何从网址获取字符串的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！