| Author |
 Topic  |
|
Kristen
Test
United Kingdom
22191 Posts |
 Posted - 02/27/2010 : 00:08:29
|
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. |
 |
|
|
Balance
Starting Member
24 Posts |
Posted - 02/27/2010 : 11:50:36
|
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
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/27/2010 : 12:54:21
|
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" |
|
|
|
Balance
Starting Member
24 Posts |
Posted - 02/28/2010 : 23:24:16
|
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.
|
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/02/2010 : 13:45:58
|
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 |
|
|
|
Topic |
|
|
|
Article: Regular Expressions in T-SQL
