2008-11-18

Email addrees verification in SQL

The function is loosely based on the email validation function by Joseph Gama.

create function [dbo].[ufn_IsValidEmail](@email varchar(400))
--Returns 1 if the string is a valid email address 0 otherwise
--
-- The following assumptions are made:
-- 1. Level domain is 2, 3 or 4 characters long
-- 2. For the local part of email the most inclusive set of characters
-- is used. Adjust it as needed after NOT LIKE statement
-- in verify local section
-- 3. Though variable are properly allocated for all parts of email
-- actual verification of the length of email or its parts not done!
--
returns bit
as
BEGIN

DECLARE
@local VARCHAR(64), @domain VARCHAR(256),
@zone VARCHAR(64), @i int, @j int, @result bit
select @result=0
select @email = lower(@email)


if not @email like '_%@_%.__%' or
@email like '%..%' or
@email like '.%' or
@email like '%@.%' or
@email like '%.@%' or
@email like '%.' goto done

select @i=CHARINDEX('@',@email)
select @local=LEFT(@email,@i-1)

select @j = CHARINDEX('.',reverse(@email))
if @j < 3 goto done
select @zone=RIGHT(@email,@j - 1)
select @domain=substring(@email,@i+1,len(@email)-@i-@j)

-- verify zone
IF (LEN(@zone)=4) AND (@zone NOT LIKE '[a-z][a-z][a-z][a-z]')
GOTo done
IF (LEN(@zone)=3) AND (@zone NOT LIKE '[a-z][a-z][a-z]')
GOTo done
IF (LEN(@zone)=2) AND (@zone NOT LIKE '[a-z][a-z]')
GOTo done
select @i=1

-- verify local
WHILE (@i<LEN(@local))
BEGIN
IF SUBSTRING(@local,@i,1) NOT LIKE '[-''a-z0-9_.!#$%&*+/=?^_`{|}~]'
GOTo done
select @i=@i+1
END

-- verify domain
select @i=1
WHILE (@i<LEN(@domain))
BEGIN
IF SUBSTRING(@domain,@i,1) NOT LIKE '[-a-z0-9.]'
GOTo done
select @i=@i+1
END
-- All correct, return success
select @result = 1
done:
return @result
end

2008-07-22

Vertical guides in SQL Server Management Studio (SSMS)


A well known registry key would add vertical guides to the code view in all versions of Visual Studio IDE.
I got curious if the same trick can be applied to the Query Editor in SQL Server 2005 Management Studio. Apparently, it can be done and here's the «secret» registry key:

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\Text Editor] "Guides"="RGB(220,220,220) 8, 16, 24, 60, 80"
This will add my favorite kind of guides. You can adjust color and position of the guides as it suites your.

As usual, be ware, you are making changes to your registry!