Came across a problem where it was needed to check a password field to be alphanumeric with no repeating chars (Why would anybody need that ???). This is another good example for using the udf to implement a check constraint ....
use pubs
go
create table tally (id int)
GO
SET NOCOUNT ON
GO
declare @i int,@count int
select @i =1, @count = 100
while @i < @count
begin
insert into tally select @i
select @i = @i + 1
end
GO
SET NOCOUNT OFF
GO
Create FUNCTION [udf_PwdCheck] (@pwd varchar(40))
RETURNS INT
AS
BEGIN
DECLARE @return_value bit
declare @checkstring varchar(50)
select @checkstring = 'abcdefghijklmnopqrstuvwxyz1234567890'
if exists (
select
partstr,
count(position)
from
(
select
distinct substring (@checkstring,t1.id,1) partstr ,charindex(substring (@checkstring,t1.id,1),@pwd,t2.id) position
from
tally t1,(select @pwd 'pwd') pwd, tally t2
where
t1.id <= len(@checkstring)
and charindex(substring (@checkstring,t1.id,1),@pwd,t2.id) <> 0
and t2.id <= len(@checkstring)
) a
group by
partstr
having
count(position) > 1
)
set @return_value = 0
else
begin
if (@pwd like '%[1-9]%' and @pwd like '%[a-z]%')
set @return_value = 1
ELSE
set @return_value = 0
end
return @return_value
END
go
CREATE TABLE [user_master] (
[userid] [varchar] (20)NULL ,
[pwd] [varchar] (40) check (dbo.udf_PwdCheck(pwd) =1)
) ON [PRIMARY]
GO
insert into user_master select 'Enigma','I am the 1'
insert into user_master select 'Brett','Bring on the Margaritas!!'
insert into user_master select 'blindman', 'Where is my dog ?'
go
drop table user_master
drop function udf_PwdCheck
drop table tally