使用检查约束验证 url

问题描述

我想创建一个验证 url 的 CHECK CONSTRAINT.

I would like to create a CHECK CONSTRAINT which validates the url.

这是我的第一种方法，但它也需要标签.

Here is my first approach, but it also takes hashtags.

CHECK ([ServiceURL] LIKE 'https://[a-z0-9]%.[a-z0-9]%')

另一个想法是这个

CHECK ([ServiceURL] LIKE 'https://[a-z0-9].[a-z0-9]')

但这也行不通.有什么想法吗?

But this doesn't work as well. any ideas?

推荐答案

进一步以 Gary 的例子为例，下面的版本展示了我们如何在不使用游标的情况下编写函数以及如何将检查约束添加到表中.您可能希望扩展它以处理 http 和 https，以及可能的其他修改(例如允许/字符)

Taking Gary's example further, the version below shows how we can write the function without the use of a cursor and how we can add the check constraint to the table. You'd likely want to extend this to cope with http as well as https, and possibly other modifications (like allowing the / character)

CREATE FUNCTION dbo.IsValidURL (@Url VARCHAR(200))
RETURNS INT
AS
BEGIN
    IF CHARINDEX('https://', @url) <> 1
    BEGIN
        RETURN 0;   -- Not a valid URL
    END

    -- Get rid of the http:// stuff
    SET @Url = REPLACE(@URL, 'https://', '');

    -- Now we need to check that we only have digits or numbers
    IF (@Url LIKE '%[^a-zA-Z0-9]%')
    BEGIN
        RETURN 0;
    END

    -- It is a valid URL
    RETURN 1;
END

-- Create the table
CREATE TABLE dbo.MyTableOnlyLettersAndDigits 
(
    id int identity(1,1) primary key clustered,
    ServiceURL varchar(200) not null
);

GO

-- Add the check constraint
ALTER TABLE dbo.MyTableOnlyLettersAndDigits 
    ADD CONSTRAINT chkValidURLs CHECK (dbo.IsValidURL(ServiceURL) = 1);


-- Some tests to show it works
insert into dbo.MyTableOnlyLettersAndDigits(ServiceURL)
values ('Missing prefix.Invalid');

insert into dbo.MyTableOnlyLettersAndDigits(ServiceURL)
values ('https://IamAValidURL123');

insert into dbo.MyTableOnlyLettersAndDigits(ServiceURL)
values ('https://Invalid#Char');