尝试在 MySQL 中使用 SQL 函数

Question

我正在尝试使用 这个script（从 URL 中提取域）：

CREATE FUNCTION [dbo].[parseURL]  (@strURL varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
IF CHARINDEX('http://',@strURL) > 0 OR CHARINDEX('https://',@strURL) > 0
SELECT @strURL = REPLACE(@strURL,'https://','')
SELECT @strURL = REPLACE(@strURL,'http://','')
SELECT @strURL = REPLACE(@strURL,'www','')
-- Remove everything after "/" if one exists
IF CHARINDEX('/',@strURL) > 0 (SELECT @strURL = LEFT(@strURL,CHARINDEX('/',@strURL)-1))    
RETURN @strURL
END

使用此 SQL 命令：

SELECT COUNT(*) as theCount, dbo.parseURL(url) as url FROM hit
WHERE url IS NOT NULL
GROUP BY dbo.parsedomain(url)
ORDER BY thecount DESC

但我收到此错误：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[dbo].[parseURL] (@strURL varchar(1000)) RETURNS varchar(1000) AS BEGIN IF ' at line 1

我对 SQL 和 MySQL 都比较陌生，因此不确定错误是 SQL-general 还是 MySQL -具体的。我缺少什么？

Answer 1

天啊，小马，努力都白费了，但就是这样：

DELIMITER |
CREATE FUNCTION DOMAIN (url VARCHAR(255)) 
 RETURNS VARCHAR(255) DETERMINISTIC
  BEGIN
   DECLARE pos INT;
    SET pos := LOCATE('://', url);
    SET url := CASE WHEN pos=0 THEN url ELSE SUBSTR(url, pos+3) END;
    SET url := REPLACE(url,'www.',''); 
    RETURN SUBSTRING_INDEX(url, '/', 1);
   END

然后查询：

  SELECT COUNT(*) as theCount,    
         DOMAIN(url) as url    
    FROM hit   
   WHERE url IS NOT NULL   
GROUP BY url   
ORDER BY thecount DESC  

我的函数的优点是剥离任何协议，https、http、ftp，等等

Answer 2

这是等效的 MySQL 函数：

DELIMITER $

CREATE FUNCTION parseURL(strURL VARCHAR(200)) 
  RETURNS varchar(200) 
BEGIN

  IF INSTR(strURL, 'http://') > 0 OR INSTR(strURL, 'https://') > 0 THEN
    SET strURL = REPLACE(strURL,'https://','');
    SET strURL = REPLACE(strURL,'http://','');
    SET strURL = REPLACE(strURL,'www','');
    -- Remove everything after "/" if one exists
    IF INSTR(strURL, '/') > 0 THEN 
     SET strURL = LEFT(strURL, INSTR(strURL, '/')-1);
   END IF;
  END IF;

  RETURN strURL;
END

使用此查询：

  SELECT COUNT(*) as theCount, 
         parseURL(url) as url 
    FROM hit
   WHERE url IS NOT NULL
GROUP BY parseURL(url)
ORDER BY thecount DESC