来自 URL SQL 的子字符串域名 [英] Substring domainname from URL SQL

问题描述

我有一组数据:

<前>www.google.com.sgwww.yahoo.com市场观察必应网英国广播公司

有些数据有www.，有些没有.有些有 .com/.com.sg/.com.ul，有些没有.

如何仅提取名称，例如google、yahoo、marketwatch、bing、bbc 使用 SQL?

解决方案

使用 CHARINDEX 和 SUBSTRING 的 MS SQL Server 语法，您可以执行类似...

(故意过度拆分以使每个步骤都显而易见.)

WITHurl_start AS(选择*,CASE WHEN LEFT(myURL, 4) = 'www.'THEN 4 ELSE 1 END AS d_start从我的表),url_end作为(选择*,情况何时CHARINDEX('.', myURL, d_start) = 0然后LEN(myURL) + 1别的CHARINDEX('.', myURL, d_start)结束为 d_end从url_start)选择*,SUBSTRING(myURL, d_start, d_end - d_start) AS 域从url_end

I have a set of Data:

www.google.com.sg
www.yahoo.com
marketwatch
bing.com
bbc.co.uk

Some data has www., some doesn't. Some has .com/.com.sg/.com.ul, some doesn't.

How do I extract just the name e.g. google, yahoo, marketwatch, bing, bbc using SQL?

解决方案

Using MS SQL Server syntax of CHARINDEX and SUBSTRING you could do something like...

(Deliberately overly split-up to mak eeach step obvious.)

WITH
  url_start AS
(
  SELECT
    *,
    CASE WHEN LEFT(myURL, 4) = 'www.' THEN 4 ELSE 1 END AS d_start
  FROM
    myTable
)
,
  url_end
AS
(
  SELECT
    *,
    CASE WHEN
      CHARINDEX('.', myURL, d_start) = 0
    THEN
      LEN(myURL) + 1
    ELSE
      CHARINDEX('.', myURL, d_start)
    END as d_end
  FROM
    url_start
)
SELECT
  *,
  SUBSTRING(myURL, d_start, d_end - d_start) AS domain
FROM
  url_end

这篇关于来自 URL SQL 的子字符串域名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！