Today's editorial is a guest post from Phil Factor as Steve is on sabbatical.
One of the things that tend to trip up newcomers to SQL Server is the fact that certain functions don’t really work as you’d expect. Some functions, such as ISNUMERIC, are entirely useless for any purpose, and are just there for backward compatibility, and possibly as a joke - try Select isNumeric(','), for example; yes, it thinks that the comma is a number: to quote MSDN ‘ISNUMERIC returns 1 for some characters that are not numbers’. You bet! The grey-muzzled developers will use the TRY_PARSE() function or create a scalar user-defined function that is appropriate for the numeric datatype whose validity they are testing.
LTRIM and RTRIM are string operators that will trap the innocent programmer. These don’t work as they do in any other language. They only trim ASCII space rather than any whitespace character. They were designed for a different problem, the fact that a long time ago, strings could be ‘packed’ with spaces when they were stored in the CHAR datatype. They aren’t the only quirky string functions: Who, for example, has been caught out by trying to use DATALENGTH to get the length of a CHAR-base string datatype?
These functions are left there purely for backward-compatibility. However, I think it is time to do a bit of spring-cleaning on these string functions. I’d draw the curtains to let the light in, throw out the dead ISNUMERIC, fix QUOTENAME so it only quotes the name if necessary, send SOUNDEX and DIFFERENCE to the museum, add the missing ‘start_location’ parameter to PATINDEX, give REPLACE the facility to use wildcards, fix SUBSTRING so that if you leave out the length parameter, it returns the whole string from the start position. Then, after a tidy-up like that, what should be added? Well, The ANSI standard TRIM and OVERLAY should be there, I suppose. I’d add a simple STRLEN function, of course. I’d probably leave it since I like the minimalist uncluttered look, unlike what you find in MySQL.
What else would you add by way of built-in string operators and functions?
