Posted by Tony Rogerson on 11/02/06 06:58

And just how do you get that working in a CONSTRAINT .... CHECK( .... ) ?
You can't without using CLR - try again.

> 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.

We use RegularExpressions in .NET, its one simple class and about 3 lines of
code - you pick a regular expression from the standard list of expressions
from google - simple.

That's how developers work, if you'd bother to go out and get some real
industrial experience you'd know that - being class room bound and
speculating how the current DBA shop works is very dangerous and cowboyish
at best.

Most code is written in C# or VB.NET and to my knowledge only a couple of
langauges will work in the CLR because of some restrictions required by SQL
Server.

>
> 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 ..
>

Wrong, wrong wrong; if you actually would bother to read and learn CLR
fundementals and how they work with SQL Server you'd know how the data types
really work and that the SQL types are exposed in the CLR.

SQL Server DBA's are required to know VB.NET becuase of Integration Services
"period".

C# and VB.NET look almost identical when you are developing, the syntax
varies but the logic and class names are all the same.

Will you PLEASE go and do some bloody research and stop guessing,
speculating on how things work.

You've STILL not shown how YOU would write this into a CHECK CONSTRAINT
without using CLR.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1162422718.509968.121450@f16g2000cwb.googlegroups.com...
>>> 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|biz|cat|com|coop|edu|gov|info|int|jobs|mil|mobi|museum|name|net|org|pro|travel)
>
> 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 one> THEN 'F'
> WHEN <illegal char in target> THEN 'F'
> WHEN <invalid suffix> THEN 'F'
> Etc.
> WHEN <LIKE predicate from Hell> THEN '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 ..
>

[Back to original message]