更新时间:2023-02-26 13:10:04
进一步以 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');