Sequential Number in an Update

[eric.nave]

this is a slight change to a fequently asked question around here. I
have a table which contains a "sortorder" column where a user can
specify some arbitrary order for records to be displayed in. Users
have sometimes ordered records the way we used to number lines in a
BASIC program (10,20,30, etc.). I'd like to do an update query and fix
this so that every record is in sequential order. I found an example
in this newsgroup of how to do this.

However, I have a slight problem! Sometimes, the users duplicated the
sortorders. So for example, I might have two records were the
sortorder is 20. The query I found in this newsgroup does not work in
that case. Here is some code so that you can see what I mean.

create table sorttest (
label char(5),
sortorder int
)
go
insert sorttest values ('joe',20)
insert sorttest values ('dan',10)
insert sorttest values ('jim',44)
insert sorttest values ('tom',20)
insert sorttest values ('jan',50)

-- data dump
select label, sortorder from sorttest order by sortorder

-- I'd like to fix all of the sortorder fields so that they are
sequential
update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortorder <= subquery.sortorder
)

-- note that tom and joe BOTH HAVE SORTORDER = 4
select label, sortorder from sorttest order by sortorder

drop table sorttest
Thanks in advance for any help.

[markc600]

You need additional criteria to sort uniquely.
Try this

update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortorder < subquery.sortorder
or (sorttest.sortorder = subquery.sortorder
and sorttest.label <= subquery.label)
)

[masri999]

If it is one tine update you can do this . Note values of sortcolumns
in all the rows will be changed

DECLARE @sortcol INT
SET @sortcol = 0 -- You can set to any other value if you like
UPDATE sorttest
SET @sortcol = sorcol = @sortcol + 1

M A Srinivas
er*******@gmail.com wrote:

this is a slight change to a fequently asked question around here. I
have a table which contains a "sortorder" column where a user can
specify some arbitrary order for records to be displayed in. Users
have sometimes ordered records the way we used to number lines in a
BASIC program (10,20,30, etc.). I'd like to do an update query and fix
this so that every record is in sequential order. I found an example
in this newsgroup of how to do this.

However, I have a slight problem! Sometimes, the users duplicated the
sortorders. So for example, I might have two records were the
sortorder is 20. The query I found in this newsgroup does not work in
that case. Here is some code so that you can see what I mean.

create table sorttest (
label char(5),
sortorder int
)
go
insert sorttest values ('joe',20)
insert sorttest values ('dan',10)
insert sorttest values ('jim',44)
insert sorttest values ('tom',20)
insert sorttest values ('jan',50)

-- data dump
select label, sortorder from sorttest order by sortorder

-- I'd like to fix all of the sortorder fields so that they are
sequential
update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortorder <= subquery.sortorder
)

-- note that tom and joe BOTH HAVE SORTORDER = 4
select label, sortorder from sorttest order by sortorder

drop table sorttest
Thanks in advance for any help.

[--CELKO--]

>have a table which contains a "sortorder" column where a user can specify some arbitrary order for records to be displayed in. <<

The classic error of mixing display and data in the RDBMS?

>Users have sometimes ordered records [sic] the way we used to number lines in a BASIC program (10, 20, 30, etc.). I'd like to do an update query and fix this so that every record [sic] is in sequential order. <<

Rows and records are totally diffreent concepts. You are still
thinking about a file system, not RDBMS. Line numbers are a physical
representation of a paper form or input screen, not a logical data
model element.

>However, I have a slight problem! Sometimes, the users duplicated the sortorders. <<

Gee, do you suppose that might be due to the fact that they are not
relational, have no rules for validation or verifiication? And the
fact that you do not have a UNIQUE constraint on this silly column????

>Thanks in advance for any help. <<

You might want to read book on RDBMS, data modeling and tiered
architectures

[David Portas]

er*******@gmail.com wrote:

this is a slight change to a fequently asked question around here. I
have a table which contains a "sortorder" column where a user can
specify some arbitrary order for records to be displayed in. Users
have sometimes ordered records the way we used to number lines in a
BASIC program (10,20,30, etc.). I'd like to do an update query and fix
this so that every record is in sequential order. I found an example
in this newsgroup of how to do this.

However, I have a slight problem! Sometimes, the users duplicated the
sortorders. So for example, I might have two records were the
sortorder is 20. The query I found in this newsgroup does not work in
that case. Here is some code so that you can see what I mean.

create table sorttest (
label char(5),
sortorder int
)
go
insert sorttest values ('joe',20)
insert sorttest values ('dan',10)
insert sorttest values ('jim',44)
insert sorttest values ('tom',20)
insert sorttest values ('jan',50)

-- data dump
select label, sortorder from sorttest order by sortorder

-- I'd like to fix all of the sortorder fields so that they are
sequential
update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortorder <= subquery.sortorder
)

-- note that tom and joe BOTH HAVE SORTORDER = 4
select label, sortorder from sorttest order by sortorder

drop table sorttest
Thanks in advance for any help.

WITH t AS
(SELECT label, sortorder,
ROW_NUMBER() OVER (ORDER BY sortorder,label) AS new_order
FROM sorttest)
UPDATE t SET sortorder = new_order;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[eric.nave]

--CELKO-- wrote:

>
The classic error of mixing display and data in the RDBMS?

This RDBMS is driving a content management system. The table in
question contains a list of URL links - basically a navigation element
for the website. So obviously, the CMS user has a requirement to put
the links in some arbitrary order that makes sense to them.

Imagine that this was the data that I wanted to have on this navigation
element:

http://google.com
http://apple.com

Pray tell how YOU would store those values so that the resulting HTML
page shows them in the order that the user wants?

fact that you do not have a UNIQUE constraint on this silly column????

If you stop and think about this (but that seems to be a handicap for
you) you will realize that there can't be a UNIQUE constraint on the
sortorder column. If you honestly can't figure out why, ask nicely and
maybe I'll explain it to you.

But I really would love to hear how you would keep a list of links in
an arbitrary order

[--CELKO--]

>there can't be a UNIQUE constraint on the sortorder column.<<

What is the nature of a sort on a set? It is a sequence; every element
has a unique position in that sequence. This is by definition.

>But I really would love to hear how you would keep a list of links in an arbitrary order <<

Now we have some more specs to use.

CREATE TABLE UserLinks
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
preference_nbr INTEGER NOT NULL
CHECK (preference_nbr 0),
content_url VARCHAR (255) NOT NULL
CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
need
PRIMARY KEY (user_id, preference_nbr)); -- here is your unique
constraint

You will need a proc to move them around once they are in the table.

CREATE PROCEDURE SwapUserLinks
(@my_user_id INTEGER, @old_preference_nbr INTEGER, @new_preference_nbr
INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= CASE preference_nbr
WHEN @old_preference_nbr
THEN @new_preference_nbr
ELSE preference_nbr + SIGN(@old_preference_nbr - @new_pos)
END
WHERE user_id = @my_user_id
AND (preference_nbr BETWEEN @old_preference_nbr AND
@new_preference_nbr
OR preference_nbr BETWEEN @new_preference_nbr AND
@old_preference_nbr);

Play with it a little bit. It takes a starting position in the list
and moves it up or down to the new position and slides the elements in
between either up or down.

When you want to drop a few rows, remember to close the gaps with this
code. It will start the numbering at 1 within each user.

CREATE PROCEDURE CloseUserLinksGaps(@my_user_id INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= (SELECT COUNT (U1.preference_nbr)
FROM UserLinks AS U1
WHERE U1.preference_nbr <= UserLinks.preference_nbr
AND user_id = @my_user_id
AND @my_user_id = UserLinks.user_id);

[--CELKO--]

>there can't be a UNIQUE constraint on the sortorder column.<<

What is the nature of a sort on a set? It is a sequence; every element
has a unique position in that sequence. This is by definition.

>But I really would love to hear how you would keep a list of links in an arbitrary order <<

Now we have some more specs to use.

CREATE TABLE UserLinks
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
preference_nbr INTEGER NOT NULL
CHECK (preference_nbr 0),
content_url VARCHAR (255) NOT NULL
CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
need
PRIMARY KEY (user_id, preference_nbr)); -- here is your unique
constraint

You will need a proc to move them around once they are in the table.

CREATE PROCEDURE SwapUserLinks
(@my_user_id INTEGER, @old_preference_nbr INTEGER, @new_preference_nbr
INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= CASE preference_nbr
WHEN @old_preference_nbr
THEN @new_preference_nbr
ELSE preference_nbr + SIGN(@old_preference_nbr - @new_pos)
END
WHERE user_id = @my_user_id
AND (preference_nbr BETWEEN @old_preference_nbr AND
@new_preference_nbr
OR preference_nbr BETWEEN @new_preference_nbr AND
@old_preference_nbr);

Play with it a little bit. It takes a starting position in the list
and moves it up or down to the new position and slides the elements in
between either up or down.

When you want to drop a few rows, remember to close the gaps with this
code. It will start the numbering at 1 within each user.

CREATE PROCEDURE CloseUserLinksGaps(@my_user_id INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= (SELECT COUNT (U1.preference_nbr)
FROM UserLinks AS U1
WHERE U1.preference_nbr <= UserLinks.preference_nbr
AND user_id = @my_user_id
AND @my_user_id = UserLinks.user_id);

[Tony Rogerson]

CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you

Interesting, I would really like to know how you would validate this as a
URL (or email address) in a constraint.

You need to look for a lot more that http:// and .com; there is http / https
for a start, ftp: there is .com, .co.uk, .net and about 2 dozen other
domains and there is sub-domains to handle.....

The only way you are going to write a safe constraint like this is to use
regular expressions and for that you'll need CLR.

So, come on celko - just how would you write this contraint to truely
validate the url is valid and also protect yourself from a DBA making an
accidental update (or melicious....)??????

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11**********************@e64g2000cwd.googlegr oups.com...

>>there can't be a UNIQUE constraint on the sortorder column.<<

What is the nature of a sort on a set? It is a sequence; every element
has a unique position in that sequence. This is by definition.

>>But I really would love to hear how you would keep a list of links in an
arbitrary order <<

Now we have some more specs to use.

CREATE TABLE UserLinks
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
preference_nbr INTEGER NOT NULL
CHECK (preference_nbr 0),
content_url VARCHAR (255) NOT NULL
CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
need
PRIMARY KEY (user_id, preference_nbr)); -- here is your unique
constraint

You will need a proc to move them around once they are in the table.

CREATE PROCEDURE SwapUserLinks
(@my_user_id INTEGER, @old_preference_nbr INTEGER, @new_preference_nbr
INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= CASE preference_nbr
WHEN @old_preference_nbr
THEN @new_preference_nbr
ELSE preference_nbr + SIGN(@old_preference_nbr - @new_pos)
END
WHERE user_id = @my_user_id
AND (preference_nbr BETWEEN @old_preference_nbr AND
@new_preference_nbr
OR preference_nbr BETWEEN @new_preference_nbr AND
@old_preference_nbr);

Play with it a little bit. It takes a starting position in the list
and moves it up or down to the new position and slides the elements in
between either up or down.

When you want to drop a few rows, remember to close the gaps with this
code. It will start the numbering at 1 within each user.

CREATE PROCEDURE CloseUserLinksGaps(@my_user_id INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= (SELECT COUNT (U1.preference_nbr)
FROM UserLinks AS U1
WHERE U1.preference_nbr <= UserLinks.preference_nbr
AND user_id = @my_user_id
AND @my_user_id = UserLinks.user_id);

[--CELKO--]

>I would really like to know how you would validate this as a URL (or email address) in a constraint. <<

I would Google it and find this website, which is full of handy regular
expressions for my constraints. This page gives ten answers.

http://regexlib.com/Search.aspx?k=URL

I guess cowboys post first and Google later :)

> The only way you are going to write a safe constraint like this is to use regular expressions and for that you'll need CLR. <<

No CLR, Tony. You need the SIMILAR TO operator in SQL-92 or a vendor
grep() functions such as Oracle and DB2 have. In SQL Server, you will
still have an ugly translation today. The x|y operator becomes <exp>
LIKE x OR <exp:LIKE y and so forth. There was an old public domain
parser for grep() to SQL Server dialect, but I cannot find it on my
disk. It probably should be re-done with CASE expressions.

>How would you write this constraint to truly validate the URL is valid and also protect yourself from a DBA making an accidental update (or malicious....)? <<

Nothing can protect you from a malicious DBA; with full DBA powers he
can destroy the world.

"On two occasions I have been asked, 'Pray, Mr. Babbage, if you put
into the machine wrong figures, will the right answers come out?' I am
not able rightly to apprehend the kind of confusion of ideas that could
provoke such a question." -- Charles Babbage

But you are confusing two different concepts; validation and
verification. A constraint can only validate a data element (This is a
syntactically correct URL) and verification (This URL actually belongs
to someone). Verification has to be external to the schema by
definition.

The way people verify a URL is to send an email to it. It is like
having a GPS tool or sextant to verify a (long, lat) location.

[Tony Rogerson]

I thought you'd try and wriggle your way out of that one.

I would Google it and find this website, which is full of handy regular
expressions for my constraints. This page gives ten answers.

http://regexlib.com/Search.aspx?k=URL

And just how the hell are you going to put that in a CONSTRAINT using
standard SQL ?? - short answer - you can't which is my whole point.

You need the SIMILAR TO operator in SQL-92 or a vendor
grep() functions such as Oracle and DB2 have.

Not available, so you advocate a vendor grep function?

Judging by your response here and other stuff I've seen you write about CLR
you've not a clue, you are giving comment on something you know nothing
about - very dangerous game to play and cowboyish at best.

In SQL Server, you will
still have an ugly translation today. The x|y operator becomes <exp>
LIKE x OR <exp:LIKE y and so forth. There was an old public domain
parser for grep() to SQL Server dialect, but I cannot find it on my
disk. It probably should be re-done with CASE expressions.

My definition of a cowboy is somebody who frigs a job, somebody who says it
will take 2 days when in reality it should take an hour, somebody who over
complicates things so they get asked back because they are the only person
who understand whats been developed, so the real cowboy is not me here
celko.

The use of CLR in this context will allow you to code a function that uses
the regex library, that same function can be used in SQL Server and in
applications - its a common re-useable component across the entire
application; no more frigging string manipulation in SQL.

But you are confusing two different concepts; validation and
verification. A constraint can only validate a data element (This is a
syntactically correct URL) and verification (This URL actually belongs
to someone). Verification has to be external to the schema by
definition.

I am confusing nothing; I'm talking about validating the correct syntax of
the URL not that it exists, you can never know that because the existance of
the URL is outside the scope of the transaction, you have no consistency
control over the third party DNS server nor domain registration entity.

The way people verify a URL is to send an email to it. It is like
having a GPS tool or sextant to verify a (long, lat) location.

Wrong again, a URL may not have an MX record so emailing means nothing - the
URL may still be valid in terms of its existance even if you can't send
email to it.

I ask you again - how would YOU write this check constraint to validate the
URL is syntactically correct in STANDARD SQL? You'd kludge it even though
other features are available to give code reuse, maintainability etc...

And as for portability, thats just mythical for constraints, ORacle and SQL
Server both have different default isolations so a number of check
constraints just won't work properly.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...

>>I would really like to know how you would validate this as a URL (or
email address) in a constraint. <<

I would Google it and find this website, which is full of handy regular
expressions for my constraints. This page gives ten answers.

http://regexlib.com/Search.aspx?k=URL

I guess cowboys post first and Google later :)

>> The only way you are going to write a safe constraint like this is to
use regular expressions and for that you'll need CLR. <<

No CLR, Tony. You need the SIMILAR TO operator in SQL-92 or a vendor
grep() functions such as Oracle and DB2 have. In SQL Server, you will
still have an ugly translation today. The x|y operator becomes <exp>
LIKE x OR <exp:LIKE y and so forth. There was an old public domain
parser for grep() to SQL Server dialect, but I cannot find it on my
disk. It probably should be re-done with CASE expressions.

>>How would you write this constraint to truly validate the URL is valid
and also protect yourself from a DBA making an accidental update (or
malicious....)? <<

Nothing can protect you from a malicious DBA; with full DBA powers he
can destroy the world.

"On two occasions I have been asked, 'Pray, Mr. Babbage, if you put
into the machine wrong figures, will the right answers come out?' I am
not able rightly to apprehend the kind of confusion of ideas that could
provoke such a question." -- Charles Babbage

But you are confusing two different concepts; validation and
verification. A constraint can only validate a data element (This is a
syntactically correct URL) and verification (This URL actually belongs
to someone). Verification has to be external to the schema by
definition.

The way people verify a URL is to send an email to it. It is like
having a GPS tool or sextant to verify a (long, lat) location.

[Hugo Kornelis]

On 30 Oct 2006 12:50:05 -0800, --CELKO-- wrote:

>>I would really like to know how you would validate this as a URL (or email address) in a constraint. <<

I would Google it and find this website, which is full of handy regular
expressions for my constraints. This page gives ten answers.

http://regexlib.com/Search.aspx?k=URL

I guess cowboys post first and Google later :)

Hi Joe,

I guess cowboys don't google for regexp when they have to work with a
product that doesn't support them.

>> The only way you are going to write a safe constraint like this is to use regular expressions and for that you'll need CLR. <<

No CLR, Tony. You need the SIMILAR TO operator in SQL-92

Unfortunately, not implemented in MS SQL Server. Let's for arguments
sake assume that the client wants his database done BEFORE Microsoft
launches the next major release of SQL Server.

or a vendor
grep() functions such as Oracle and DB2 have.

No, NO, NO!!!!! You still don't understand it, do you?

The proprietary features change from release to release (BIT with and
without NULL, etc.) . Whatever a proprietary feature does is
determined by the vendor. Standard SQL is external and eventually the
vendor has to come into alignment.

Standard code means that another programmer can read and maintain it --
a great threat to Cowboy Coders who depend on tricks in the dialect to
hold their job. But maintainable code is one of the marks of a
professional.

Standard code means that the DB can be moved to another platform with
minimal effort -- another great threat to Cowboy Coders who speak only
in one dialect. Portable code is another marks of a professional.

We do not even write compilers or OS in assembly languages any more.
The lowest level languages used for those tasks is Standard C. Gee,
did you ever wonder why nobody is takign advantage of all the neat
hardware level "documented proprietary features" they coudl be using?

So DON'T use the proprietary grep() functions if you want to look like a
professional. Only cowboy coders would do that. Real professionals use
ANSI standard code only!

BTW, grep() is not implemented in MS SQL Server either, so even if you
do want to become a cowboy coder, you're still out of luck in this case.

In SQL Server, you will
still have an ugly translation today.

Yep. And I believe that was what Tony was asking you about. A question
that you appear to be attempting to dodge, by drowning your lack of a
real answer in lots of words.

So I'll join in the challenge - how would your constraint look like if
it _has_ to work on MS SQL Server 2005?

Here's mine:
CHECK (Assemblies.ValidURL(ColumnWithURL) = 'valid')
where Assemblies.ValidURL is a small, well-documented and carefully
managed CLR function that uses one of the regexps you so conveniently
located for me to do the actual check.

>The way people verify a URL is to send an email to it. It is like
having a GPS tool or sextant to verify a (long, lat) location.

Funny. Each time I try to send e-mail to http://www.google.com, Outlook
Express complains that the address is not okay. Probably not a valid
URL, then.

--
Hugo Kornelis, SQL Server MVP

[--CELKO--]

grep() functions such as Oracle and DB2 have.

>Not available, so you advocate a vendor grep function? <<

No, I advocate the Standard SIMILAR TO operator in SQL-92. I will use
a vendor function in practice until they catch up, then change the code
as part of perfective maintenance. I have always made a distinction
among Standard SQL, portable SQL and proprietary SQL.

>Judging by your response here and other stuff I've seen you write about CLR you've not a clue, you are giving comment on something you know nothing about - very dangerous game to play and cowboyish at best. <<

The rest of the trade press also seems to be reporting that I am not
alone in finding problems with CLR.

> My definition of a cowboy is somebody who frigs a job, somebody who says it will take 2 days when in reality it should take an hour, <<

My definition of a cowboy is somebody takes an hour to do what should
take two days. These days, he say he is "agile" or "extreme"
programming

> somebody who over complicates things so they get asked back because they are the only person who understand whats been developed <<

"Everything should be made as simple as possible, but not simpler."
-- attributed to Albert Einstein

And what better to get that kind of "job secure programming" than
proprietary code?

>The use of CLR in this context will allow you to code a function that uses the regex library, that same function can be used in SQL Server and in applications - its a common re-useable component across the entire application; no more frigging string manipulation in SQL. <<

And the database side of the house has to maintain both SQL and the
dozen or so CLR languages from which these functions will come. How is
your Haskell? It is a great language for grep() and it is declarative
like SQL! I happen to like Algol 60 and Pascal, so why not put them
into the schema?

>I'm talking about validating the correct syntax of the URL not that it exists, you can never know that because the existance of the URL is outside the scope of the transaction, you have no consistency control over the third party DNS server nor domain registration entity. <<

I think I said that ..

>a URL may not have an MX record so emailing means nothing - the URL may still be valid in terms of its existance even if you can't send email to it. <<

Then it is a bit useless for e-commerce, isn't it? Apparently, not
having a Mail Exchanger ius not a problem for all the sites that are
using it as their custrmer id.

[Tony Rogerson]

As ever you try and direct the post away from what you've been asked; you've
followed the typical celko pattern a) change the subject to avoid the
problem, b) throw in a quote to some scolar to apparently make us aware you
appear to be educated and c) throw out a few dozen more insults.

No, I advocate the Standard SIMILAR TO operator in SQL-92. I will use
a vendor function in practice until they catch up, then change the code
as part of perfective maintenance. I have always made a distinction
among Standard SQL, portable SQL and proprietary SQL.

Like I said again - how would you code this NOW in SQL Server; would you use
a ton of LIKE's and CASE's or would you use the tried, trusted and
recommended method for complex logic and use CLR?

You've already told us "NO CLR, Tony".

And as to all the .NET languages; you are completely out of touch with
current environments, even in the enterprise DBA's know VB.NET or C# usually
the former, in practice all stuff is either VB.NET or C#.

The rest of the trade press also seems to be reporting that I am not
alone in finding problems with CLR.

Rubbish, I'm not going to get distracted from you posting the standard SQL
equiv that will work in SQL Server NOW.

Then it is a bit useless for e-commerce, isn't it? Apparently, not
having a Mail Exchanger ius not a problem for all the sites that are
using it as their custrmer id.

You are completely and utterly in the dark on how DNS works - shouldn't a
good professional consultant google for the information, digest and learn
first before trying to give opinion or technical advice? Its what I do; you
don't seem to do that if this is anything to go by.

DNS as A and MX records; SMTP uses the MX record to find the mail server. If
there is no MX record then you can't reach the mail server.

Customer ID? who on earth uses the MX record as a customer ID - they'd be
mad; take mine mail.torver.net is where my emails come in, I don't have
www.torver.net pointing anywhere; I have other backup DNS MX records all
with different sub-domains - jesus you are just such a fraud....

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.com...

grep() functions such as Oracle and DB2 have.

>>Not available, so you advocate a vendor grep function? <<

No, I advocate the Standard SIMILAR TO operator in SQL-92. I will use
a vendor function in practice until they catch up, then change the code
as part of perfective maintenance. I have always made a distinction
among Standard SQL, portable SQL and proprietary SQL.

>>Judging by your response here and other stuff I've seen you write about
CLR you've not a clue, you are giving comment on something you know
nothing about - very dangerous game to play and cowboyish at best. <<

The rest of the trade press also seems to be reporting that I am not
alone in finding problems with CLR.

>> My definition of a cowboy is somebody who frigs a job, somebody who
says it will take 2 days when in reality it should take an hour, <<

My definition of a cowboy is somebody takes an hour to do what should
take two days. These days, he say he is "agile" or "extreme"
programming

>> somebody who over complicates things so they get asked back because
they are the only person who understand whats been developed <<

"Everything should be made as simple as possible, but not simpler."
-- attributed to Albert Einstein

And what better to get that kind of "job secure programming" than
proprietary code?

>>The use of CLR in this context will allow you to code a function that
uses the regex library, that same function can be used in SQL Server
and in applications - its a common re-useable component across the
entire application; no more frigging string manipulation in SQL. <<

And the database side of the house has to maintain both SQL and the
dozen or so CLR languages from which these functions will come. How is
your Haskell? It is a great language for grep() and it is declarative
like SQL! I happen to like Algol 60 and Pascal, so why not put them
into the schema?

>>I'm talking about validating the correct syntax of the URL not that it
exists, you can never know that because the existance of the URL is
outside the scope of the transaction, you have no consistency control
over the third party DNS server nor domain registration entity. <<

I think I said that ..

>>a URL may not have an MX record so emailing means nothing - the URL may
still be valid in terms of its existance even if you can't send email to
it. <<

Then it is a bit useless for e-commerce, isn't it? Apparently, not
having a Mail Exchanger ius not a problem for all the sites that are
using it as their custrmer id.

[Greg D. Moore \(Strider\)]

"--CELKO--" <jc*******@earthlink.netwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.com...

>

>>a URL may not have an MX record so emailing means nothing - the URL may
still be valid in terms of its existance even if you can't send email to
it. <<

Then it is a bit useless for e-commerce, isn't it? Apparently, not
having a Mail Exchanger ius not a problem for all the sites that are
using it as their custrmer id.

Here you're just plain wrong Joe.

A URL is a uniform resource locator. It has nothing to do with the MX
record.

A URL contains a hostname among other parts.

The hostname contains a domain name. BUT that host name may not be easy to
pick out.

Is foo.bar.com a hostname or a domain name?

If it's a hostname, bar.com MIGHT let you look up an MX record, but there's
no guarantee.

If it's a domainname, then foo.bar.com MIGHT let you lookup the MX record,
but there's no guarantee.

So which do you test? Both?

What if it's baz.foo.bar.com? Test it three times?

>

[--CELKO--]

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

[--CELKO--]

>Here you're just plain wrong Joe. <<

Mea culpa, mea culpa, mea maxima culpa!

[Tony Rogerson]

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--" <jc*******@earthlink.netwrote in message
news:11**********************@f16g2000cwb.googlegr oups.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|job s|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 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 ..

[Damien]

--CELKO-- wrote:
[snip]

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

Please Joe,

spend a little time learning about the *COMMON* language runtime, which
also involves the *COMMON* type system - on which both VB.Net and C#
are built. That's right - for as long as VB.Net and C# have existed,
they've had no discrepancies on booleans (or any other built in types),
because they're using exactly the same implementation.

You've had a number of years to learn this Joe, please try to take it
in.

By the time the code is added into the database, it doesn't matter
whether it was written in VB.Net or C# (or any other CLR language).
Provided each language is equally powerful, the same algorithm will
have been compiled to the same IL code. And if you're not comfortable
with the language in which a piece of code is written (though any
decent .Net programmer should, IMO, at least be able to read/maintain
in either VB.Net or C#), there are plenty of tools to convert from one
language to another.

Damien

[Hugo Kornelis]

On 1 Nov 2006 15:11:58 -0800, --CELKO-- wrote:

>>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|job s|mil|mobi|museum|name|net|org|pro|travel)

Hi Joe,

The requirement was actually to check a URL, not an email address. But
I'd be just as happy with a working CHECK constraint for well-formed
email address - the actual issue here is that I believe that there ARE
things that are better handled in the CLR than in ANSI SQL.

Oh, and if you ever have to build a REAL email address validator, please
find a better one than the one above - David Thompson appears to be
unaware that people outside of the US also have mail now, since he
disallows all non-US top-level domain names.

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

So how would you incorporate this logic in a SQL Server CHECK constraint
which (as you undoubtebly know) is not allowed to access other tables or
even other rows from the same tables?

(snip)

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

I don't care if it's ugly - it's not a constraint! How is this SELECT
going to prevent someone entering data that is not well-formed?

>Is it easy to understand, if you read SQL? I think so.

It will probably no lonnger be easy to understand once it's finished (if
you are able to get it working in a CHECK constraint at all!!). The
first parts (<@ count is not oneand <illegal char in target>) will
probably be quite straightforward, The next (<invalid suffix>) less so.
And the <LIKE predicate from Hell>? Well, if that refers to the SELECT
'Valid' (...) query from the start of your post, it's definitely not
easy. I consider myself to be quite good at understanding SQL, and I had
to look hard before I understood the trick. A less experienced SQL coder
would probably not get it.

(snip)

>I think this demonstates that it is possible and could be put into a
Standard SQL procedure.

If standard SQL allows a subquery in a CHECK constraint, then you have
indeed demonstrated that it is possible in standard SQL. But the
question was to implement the constraint in SQL Server, using only
standard SQL (or rather, the subset of standard SQL that SQL Server
implements). I maintain that it is not possible.

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

In such a case, I'd do the same as what I'd do if the DB I am DBA-ing
has table and column names in PascalCase, Fred likes to code in
camelCase and so forth for a dozen different developers. I would force
all developers to follow one standard (set by me), making sure that
management backs me on this. Developers who don't follow the standard
will very soon realise that I am the one who decides what does and what
doesn't get deployed on the production server, and that following the
standards is a good way to improve the chance of getting the code
deployed.

With CLR, it's the same. I get to review the code before I install the
assembly on the production server, so it better be in a language I am
able to read. And if an email or URL validator is already installed,
there's no chance that any second such validator will be added - if the
first doesn't work satisfactory, just file a change request but don't
duplicate code.

I really don't see the big deal. If you put the code to validate in a
SQL stored procedure or user-defined function, you'd also have to deal
with various developers who might code competing versions of the same
code.

--
Hugo Kornelis, SQL Server MVP