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?
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');
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
This is an update on the function that doesn't require external functions. Also, it can also be transformed into a procedure should you wish to debug it.
DROP FUNCTION IF EXISTS IsValidURL
GO
CREATE FUNCTION [dbo].[IsValidUrl]
(
@Url varchar(100)
)
RETURNS BIT
AS
BEGIN
--PRINT('Original URL: '+@Url)
-- CLEAN THE URL
SET @Url = (SELECT REPLACE(@Url,'http://',''))
SET @Url = (SELECT REPLACE(@Url,'https://',''))
IF (CHARINDEX('/', @Url) !=0)
SET @Url = SUBSTRING(@Url, 0, CHARINDEX('/', @Url))
--PRINT('Cleaned URL: '+@Url)
IF (@Url LIKE '%[^a-zA-Z0-9.]%') -- check that the URL contains letters or the dot
BEGIN
RETURN 0;
--PRINT('0 -> Contains bad characters');
END
IF (CHARINDEX('.', @Url) = 0)
BEGIN
RETURN 0;
--PRINT('0 -> Doesn''t contain the dot character');
END
DECLARE @predot varchar(100)
DECLARE @postdot varchar(100)
WHILE ( CHARINDEX('.', @Url) != 0)
BEGIN
SET @predot = SUBSTRING(@Url, 0, CHARINDEX('.', @Url))
SET @postdot = SUBSTRING(@Url, CHARINDEX('.', @Url)+1, LEN(@Url))
--PRINT 'Pre dot: '+@predot
--PRINT 'Post dot: '+@postdot
IF (LEN(@predot) < 1)
BEGIN
RETURN 0;
--PRINT('0 -> Pre dot can''t be empty');
END
IF (LEN(@postdot) < 1)
BEGIN
RETURN 0;
--PRINT('0 -> Post dot can''t be empty');
END
SET @Url = @postdot
END
RETURN 1
--PRINT('1 -> Final return');
END
GO
