SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Regular Expressions in T-SQL
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/27/2010 :  00:08:29  Show Profile  Reply with Quote
Two problems:

1) You have declared @vchRegularExpression as VARCHAR(50), your Regex is longer than that. Use PRINT statements in your code to debug.

2) What it ${' + CAST(@intLength as varchar) + '}' on the end intended to do? I don't think that construction is relevant in the RegEx, but maybe I'm misunderstanding. Anyway, use PRINT to get the REgEx you are about to pass to dbo.find_regular_expression() and stick it in a RegEx tester to check it works as you expect, then you can have confidence that the RegEx / Data is as you expect and can move to seeing if dbo.find_regular_expression() behaves as you expect.
Go to Top of Page

Balance
Starting Member

24 Posts

Posted - 02/27/2010 :  11:50:36  Show Profile  Reply with Quote
Kristen,

Good catch on #1. I've updated the length of @vchRegularExpression to 100 (the regEx is 77).

Af of #2, I just copied the sample from the author of this function from http://www.sqlteam.com/article/regular-expressions-in-t-sql
The code sample has the exact same code I'm using.

Here's my updated code which still returns "Special characters found" when it shouldn't:

DECLARE @intLength AS INTEGER
DECLARE @vchRegularExpression AS VARCHAR(100)
DECLARE @vchSourceString as VARCHAR(50)
DECLARE @bitHasNoSpecialCharacters as BIT

-- Initialize variables
SET @vchSourceString = 'test@acme.com'

-- Get the length of the string
SET @intLength = LEN(@vchSourceString)

-- Set the completed regular expression (valid email address, per Sun Java class javax.mail.internet.InternetAddress parse() function
SET @vchRegularExpression = '^[a-zA-Z_0-9-''\+~]+(\.[a-zA-Z_0-9-''\+~]+)*@([a-zA-Z_0-9-]+\.)+[a-zA-Z]{2,7}${' + CAST(@intLength as varchar) + '}'

-- get whether or not there are any special characters
SET @bitHasNoSpecialCharacters = dbo.find_regular_expression(@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString

IF @bitHasNoSpecialCharacters = 1 BEGIN
PRINT 'No special characters.'
END ELSE BEGIN
PRINT 'Special characters found.'
END

GO

Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/27/2010 :  12:54:21  Show Profile  Reply with Quote
You haven't copied the RegEx from the article, you've modified it, and I think you've broken it in the process - as I said, I don't believe that {nnn} on the end will work, but I am not a RegEx expert

As I suggested before:

PRINT vchSourceString and vchRegularExpression as you pass them to the Function and stick them in a Regular Expression Tester to prove that you have that correct.

Beyond that I have no idea if the function still works, technically, but if your RegEx is not correctly formed to start with then you'll be looking for the problem in the wrong place.

Note that you cannot validate an Email address with a RegEx (Well, you can, but check the RFC - its a humongous RegEx code). At best you can just check that it is reasonable well formed, but you increase the risk that you will exclude some valid addresses which may, for example, prevent people registering on your site / using your service.

Doing an MX lookup is about as close as you can get to checking the address without actually sending an "Activation code"
Go to Top of Page

Balance
Starting Member

24 Posts

Posted - 02/28/2010 :  23:24:16  Show Profile  Reply with Quote
Kristen,

I'm not trying to validate whether the email address exists, I know that's not possible with SQL. All I want to do is validate whether the email address is syntactically correct. The RegEx is valid as I've tested with Perl and Java. I'm wondering if the VBScript.RegExp library is different than the libraries used by Perl and Java.

Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/02/2010 :  13:45:58  Show Profile  Reply with Quote
So what does the {nnn} on the end do?

I tested it in javascript and it didn't work. Did you test the actual regex that your code generates, or the regex that you think that your code generates - they may not be the same!!

I realise that you are not trying to test that the email address exists, but your test is quite strong, and as such it will cause some valid email addresses to be treated as invalid.

Hadn't thought about VBScript.RegExp library behaving differently, but it makes sense that you test your RegEx against that.

Edited by - Kristen on 03/02/2010 13:49:21
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000