www datatypes

[codejet]

Is not yet time for database to have the email and website/ url data types?

[Dave Kreskowiak]

Why would you ever want this? Those are just strings and SQL already handles those quite easily. There's nothing you can do to them that can't already be done with a string. Also, having a type assumes the underlying data format will never change. That simply is not that case for URLs and email addresses. Your type eventually becomes obsolete.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak

[OriginalGriff]

Why? What extra use would they have over NVARCHAR? Do you think we should add twitter hashtags as well?

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

[Brisingr Aerowing]

I know Access has a URL type, but AFAICT it is stored internally as an NVARCHAR with some specific metadata. It is basically a convenience type to allow for clicking on the URL to open the respective resource. URLs and Email Addresses are just strings. It is up to the application displaying the data to determine how to best display the value. Url/Email types would be useful in determining HOW to display the data, reducing (or possibly eliminating) the need to parse the string to find out what it is, but they should be stored internally as strings with some metadata on their type so that the database can be opened by other programs and systems that do not support a URL/Email type.

What do you get when you cross a joke with a rhetorical question? The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism. Do questions with multiple question marks annoy you???

[PIEBALDconsult]

You. Just. Never. Know. What if there were an IP address type that works just fine for IPv4, but doesn't work for IPv6? I've also seen a technique that can store a ten-digit U.S. phone number in a 32-bit integer -- that worked fine in the 80s but not today. Or a Zipcode type that can't handle Zip+4 ? What if you had a huge enterprise system that relied on that and now had to refactor everything? Or are you going to wait for a patch/update from some third-party vendor? I waited most of 1999 for Y2K fixes from a couple of vendors, and eventually (September?) they said they couldn't do it and we'd need to stop using those products.

[PIEBALDconsult]

I am also reminded of Pascal's string type, which is limited to 255 characters. X| Even Sql Server's varchar, limited to 8000; and nvarchar, 4000 characters. Some times defining types and giving them artificial limits can have dire unforeseen consequences many years in the future.

[peterchen]

They could be useful if it's not just the data type. Validation could arguably be done in a trigger. Filter / Group e.g. by protocol and/or domain seems like an applicable, useful operation. Could be done by respective functions, but I'm certain indexing could be optimized easier if the types were native. Comparison / equality works slightly different from plain strings. Can be solved by normalization before storing, but this would be considered lossy for some applications.

[Marco Bertschi]

Regex. Regex is what you are looking for.

"A property doesn't have to be a Property to be a property." - PIEBALDConsult

[Thanks7872]

# :laugh:

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning

[peterchen]

email: @"^([0-9a-zA-Z]([\+\-_\.][0-9a-zA-Z]+)*)+"@(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]*\.)+[a-zA-Z0-9]{2,17})$"; URL: (((([a-z]|\d|-|.||~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'()*+,;=]|:)*@)?(((\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5]).(\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5]).(\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5]).(\d|[1-9]\d|1\d\d|2[0-4]\d|25[0-5]))|((([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|\d|-|.||~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|\d|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF]))).)+(([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|\d|-|.|_|~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])([a-z]|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF]))).?)(:\d*)?)(/((([a-z]|\d|-|.||~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'()*+,;=]|:|@)+(/(([a-z]|\d|-|.||~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'()*+,;=]|:|@)))?)?(\?((([a-z]|\d|-|.||~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'()*+,;=]|:|@)|[\uE000-\uF8FF]|/|\?)*)?(#((([a-z]|\d|-|.||~|[\u00A0-\uD7FF\uF900-\uFDCF\uFDF0-\uFFEF])|(%[\da-f]{2})|[!\$&'()*+,;=]|:|@)|/|\?)*)?$ No, I don't.