August 17, 2004 at 9:26 am
I am trying to retrieve email ids satisfying the following conditions. I definitely don’t want to use the cursors. Can we do it in single SQL STATEMENT?
-- Only dash or dot, @, 0-9, a-z characters only.
-- should contain at least one dot and one @.
-- It cannot have more than one dot consecutively or @.
-- It should have at least 2 characters after dot.
Thanks
Shas3
August 17, 2004 at 9:51 am
This is something that I use for getting email format. It is not pure SQL but I found it RegEx is easier to check email format so I just did it that way. Once you create the function below, I just run this
---------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @RegExp AS VARCHAR(1000)
DECLARE @SourceString as VARCHAR(5000)
DECLARE @RetunCode as BIT
-- Set the completed regular expression
SET @RegExp = '^[\w\.\-\+\']+@[a-zA-Z0-9\-]+(\.[a-zA-Z0-9\-]{1,})*(\.[a-zA-Z]{2,4}){1,2}$' -- with '+' and '`' sign
SELECT *
INTO ValidEmail
FROMEmail
where master.dbo.fn_RegExp(Email, @RegExp,0,'false') = 1
----------------------------------------------------------
Function is as below.
-----------------------------------------------------------
/*
~ Name: fn_RegExp
~ Desc: Get Regular Expression on SQL
~
~ Return: 1 - Fits w/ RegExp
~ 2 - Not fits
~
~ Parameters
~ Input:
~ @source varchar(5000), -- Source String
~ @regexp varchar(1000), -- Regular Express
~ @ignorecase bit = 0, -- Ignore Upper Case
~ @global varchar(5) = 'false' -- Default is false since there is no necessary for global property for SQL
~
~
~ Author: iLoveSQL
~ Date: 12/17/2003
~ Example SET @RetunCode = dbo.fn_RegExp('hello@abc.com', [a-Z],0,'false')
*/
ALTER FUNCTION dbo.fn_RegExp
(
@source varchar(5000),
@regexp varchar(1000),
@ignorecase bit = 0,
@global varchar(5) = 'false' -- But I made this false as default since there is no necessary for global property for SQL
)
RETURNS bit
AS
BEGIN
DECLARE @hr integer
DECLARE @objRegExp integer
DECLARE @objMatches integer
DECLARE @objMatch integer
DECLARE @count integer
DECLARE @results bit
-- Make a regex object.
-- set oRegExp = New RegExp
EXEC @hr = sp_OACreate 'VBScript.RegExp',
@objRegExp OUTPUT
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
/* Set its properties. */
-- with oRegexp
-- .Pattern = "^[\w\.\-]+@[a-zA-Z0-9\-]+(\.[a-zA-Z0-9\-]{1,})*(\.[a-zA-Z]{2,4}){1,2}$"
EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
-- .Global = true
EXEC @hr = sp_OASetProperty @objRegExp, 'Global', @global
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
-- .IgnoreCase = true
EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase',
@ignorecase
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
-- Run the Method
-- if not oRegExp.Test( sEmailAddressTemp ) then
-- ...return error text here.
EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
-- Clean up
-- set oRegExp = nothing
EXEC @hr = sp_OADestroy @objRegExp
IF @hr <> 0 BEGIN
SET @results = 0
RETURN @results
END
RETURN @results
END
August 18, 2004 at 3:03 am
Yes you can do this in pure SQL. Condition by condition:
-- Only dash or dot, @, 0-9, a-z characters only.
(myfield NOT LIKE '%[^-.@0-9a-zA-Z]%')
--
should contain at least one dot and one @.
(myfield LIKE '%.%@%' OR myfield like '%@%.%')
-- It cannot have more than one dot consecutively or @.
(myfield NOT LIKE '%..%' AND myfield NOT LIKE '%@@%')
-- It should have at least 2 characters after dot.
(myfield LIKE '%.__%')
Stick together all these conditions with ANDs in a WHERE clause and you should be set.
August 18, 2004 at 10:52 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply