Re: Sequential Number in an Update
>So I'll join in the challenge - how would your constraint look like if it _has_ to work on MS SQL Server 2005? <<
This grep checks email format against RFC 3696 and was written by David
Thompson
[a-z0-9!$'*+\-_]+(\.[a-z0-9!$'*+\-_]+)*
@([a-z0-9]+(-+[a-z0-9]+)*\.)+
([a-z]{2}aero|arpa|bi z|cat|com|coop| edu|gov|info|in t|jobs|mil|mobi |museum|name|ne t|org|pro|trave l)
Let's translate it. The SQL Server LIKE predicate is missing the
Kleene plus + and Kleene star * repetition tokens. We can fake those
with a table of repetitions and a JOIN. The * table has an empty
string in it in addition to repetitions of the character search
pattern.
CREATE TABLE Symbols
(char_cnt INTEGER NOT NULL PRIMARY KEY,
token VARCHAR(255) NOT NULL);
INSERT INTO Symbols VALUES (1, '[a-z0-9!$'*+-_]');
INSERT INTO Symbols VALUES (2, '[a-z0-9!$'*+-_][a-z0-9!$'*+-_]');
Etc.
So to get a pattern for a string with a dot in it, you would use
SELECT 'Valid'
FROM Symbols AS A1, Symbols AS A2
WHERE @target LIKE A1.token + '.' + A2.token
AND LEN(@target) < char_cnt - 1;
The test for length can be improved by looking at substrings in front
of and behind the period and the suffix code, but you get the idea.
The | can be done same way
CREATE TABLE Suffixes (d VARCHAR(4) NOT NULL);
INSERT INTO Suffixes VALUES ('aero');
-- and so forth for 'arpa', 'biz', 'cat', 'com',
'coop', 'edu', 'gov', 'info', 'int', 'jobs',
'mil', 'mobi', 'museum', 'name', 'net', 'org',
'pro', 'travel'
Alternatively, use a chain of OR-equality tests on the suffix via a
substring. I would use a table of valid country codes in place of
[a-z]{2}
The final tuning trick is the CASE expression from Hell. Using the
order of execution of the WHEN clauses, test for common simple errors
and finish the monster like predicate.
SELECT ..
FROM ..
WHERE CASE
WHEN <@ count is not oneTHEN 'F'
WHEN <illegal char in targetTHEN 'F'
WHEN <invalid suffixTHEN 'F'
Etc.
WHEN <LIKE predicate from HellTHEN 'T'
ELSE 'F' END = 'T';
Is this ugly? Well, I have seen more tables with longer rows in a lot
of code posted here.
Is it easy to understand, if you read SQL? I think so.
Do I like it? No, I want my ANSI Standard SIMILAR TO predicate after
over a decade of waiting. The LIKE predicate is very weak and we are
using more complex encodings, like email addresses, in modern data. We
got OUTER JOINs, so I live in hope (but they did take MERGE out of 2005
after having it in a Beta).
I think this demonstates that it is possible and could be put into a
Standard SQL procedure.
What do you do when the DB you are DBA-ing has the grep you like in
your favorite CLR language, the grep fred likes in his favorite CLR
language, and so forth for a dozen different developers? What if one
grep is not like another grep? Look at how many versions of grep we
have in Unix alone.
The DBA group really cannot learn those languages and stay current in
them. We now need two application programmers per language (always
hire in pairs for the same reasons you make back ups). So we pick a
limited set of CLR languages for the DB shop. Sure hope that the
application users agree witht he choice. If they are on VB and we are
on C#, that +1, -1 thing in Booleans could be a problem ..
>So I'll join in the challenge - how would your constraint look like if it _has_ to work on MS SQL Server 2005? <<
This grep checks email format against RFC 3696 and was written by David
Thompson
[a-z0-9!$'*+\-_]+(\.[a-z0-9!$'*+\-_]+)*
@([a-z0-9]+(-+[a-z0-9]+)*\.)+
([a-z]{2}aero|arpa|bi z|cat|com|coop| edu|gov|info|in t|jobs|mil|mobi |museum|name|ne t|org|pro|trave l)
Let's translate it. The SQL Server LIKE predicate is missing the
Kleene plus + and Kleene star * repetition tokens. We can fake those
with a table of repetitions and a JOIN. The * table has an empty
string in it in addition to repetitions of the character search
pattern.
CREATE TABLE Symbols
(char_cnt INTEGER NOT NULL PRIMARY KEY,
token VARCHAR(255) NOT NULL);
INSERT INTO Symbols VALUES (1, '[a-z0-9!$'*+-_]');
INSERT INTO Symbols VALUES (2, '[a-z0-9!$'*+-_][a-z0-9!$'*+-_]');
Etc.
So to get a pattern for a string with a dot in it, you would use
SELECT 'Valid'
FROM Symbols AS A1, Symbols AS A2
WHERE @target LIKE A1.token + '.' + A2.token
AND LEN(@target) < char_cnt - 1;
The test for length can be improved by looking at substrings in front
of and behind the period and the suffix code, but you get the idea.
The | can be done same way
CREATE TABLE Suffixes (d VARCHAR(4) NOT NULL);
INSERT INTO Suffixes VALUES ('aero');
-- and so forth for 'arpa', 'biz', 'cat', 'com',
'coop', 'edu', 'gov', 'info', 'int', 'jobs',
'mil', 'mobi', 'museum', 'name', 'net', 'org',
'pro', 'travel'
Alternatively, use a chain of OR-equality tests on the suffix via a
substring. I would use a table of valid country codes in place of
[a-z]{2}
The final tuning trick is the CASE expression from Hell. Using the
order of execution of the WHEN clauses, test for common simple errors
and finish the monster like predicate.
SELECT ..
FROM ..
WHERE CASE
WHEN <@ count is not oneTHEN 'F'
WHEN <illegal char in targetTHEN 'F'
WHEN <invalid suffixTHEN 'F'
Etc.
WHEN <LIKE predicate from HellTHEN 'T'
ELSE 'F' END = 'T';
Is this ugly? Well, I have seen more tables with longer rows in a lot
of code posted here.
Is it easy to understand, if you read SQL? I think so.
Do I like it? No, I want my ANSI Standard SIMILAR TO predicate after
over a decade of waiting. The LIKE predicate is very weak and we are
using more complex encodings, like email addresses, in modern data. We
got OUTER JOINs, so I live in hope (but they did take MERGE out of 2005
after having it in a Beta).
I think this demonstates that it is possible and could be put into a
Standard SQL procedure.
What do you do when the DB you are DBA-ing has the grep you like in
your favorite CLR language, the grep fred likes in his favorite CLR
language, and so forth for a dozen different developers? What if one
grep is not like another grep? Look at how many versions of grep we
have in Unix alone.
The DBA group really cannot learn those languages and stay current in
them. We now need two application programmers per language (always
hire in pairs for the same reasons you make back ups). So we pick a
limited set of CLR languages for the DB shop. Sure hope that the
application users agree witht he choice. If they are on VB and we are
on C#, that +1, -1 thing in Booleans could be a problem ..
Comment