Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Is It Time To Clear Out Those Quirky Functions?

By Phil Factor,

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?

Total article views: 265 | Views in the last 30 days: 5
 
Related Articles
FORUM

Convert String DataType to Date datatype in DTS

Convert String DataType to Date datatype in DTS

FORUM

isnumeric issue

isnumeric issue

FORUM

convert the datatype string to date

convert the datatype string to date

FORUM

convert datatype DT_STRING to DT_DBDATE in ssis

convert datatype DT_STRING to DT_DBDATE in ssis

FORUM

IsNumeric

Hi, May be u all know this early but i wanted to post this. I came across a phenomenon with the...

Tags
editorial    
functions    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones