Validate url with a 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]%')

Another idea was this

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

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

回答1:

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');

回答2:

The like clause has very limited pattern matching as you have discovered. It is not possible to reproduce the behavior you desire using as like clause.

You can roll your own pattern matching function as a CLR proc. You could also perform this equivalent logic in TSQL since the logic is not too complicated as a UDF. Then use the UDF in check constraint (or trigger, etc.)

Here is an article for UDF for URL validation with the function reproduced below. I have not tested personally.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[IsValidUrl]
(
    @Url varchar(100)
)

RETURNS int

AS

BEGIN

    -- CREATE THE VARIABLES
    DECLARE @TldExtension VARCHAR(10)

    -- CLEAN THE URL
    SET @Url = (SELECT REPLACE(@Url,'http://',''))
    IF (CHARINDEX('/', @Url) !=0)
        SET @Url = SUBSTRING(@Url, 0, CHARINDEX('/', @Url))

    -- DECLARE THE CURSOR
    DECLARE Tld_Cursor CURSOR FOR
        SELECT [Tld] FROM [kogd].[dbo].[TLD]
        ORDER BY [Length] DESC

    -- OPEN THE CURSOR
    OPEN Tld_Cursor
    -- SET THE VARIABLE TO THE NEXT TLD
    FETCH NEXT FROM Tld_Cursor INTO @TldExtension

    -- LOOP
    WHILE @@FETCH_STATUS = 0
        BEGIN
            -- IF FOUND RETURN 1
            IF (CHARINDEX(@TldExtension, @Url) != 0) RETURN 1
            -- ELSE GET NEXT
            FETCH NEXT FROM Tld_Cursor INTO @TldExtension
        END

    -- IF NOT FOUND RETURN 0
    RETURN 0

END

GO

---

Original article is dead, a similar article might be useful