Using LEN() Wrong

Kenneth Fisher had a recent blog post on a question that was posted to Stack Overflow.  The interesting bit was someone using the LEN() function to determine the length of a float with odd results.

Personally, it had never occurred to me to use the LEN() function against a number.  It’s a string manipulation function, and that’s it.

Here’s a little test harness:

BEGIN TRANSACTION;

-- https://sqlstudies.com/2016/06/02/using-len-wrong/
-- Kenneth Fisher
CREATE TABLE #temp (MyFloat FLOAT, MyStr VARCHAR(50));

INSERT INTO #temp VALUES (12345,'12345'),(123456789,'123456789');

SELECT MyFloat, 
    LEN(MyFloat) AS MyFloatLen, 
    DATALENGTH(MyFloat) AS MyFloatDataLen,
    MyStr, 
    LEN(MyStr) AS MyStrLen, DATALENGTH(MyStr) AS MyStrDataLen,
    LEN(LTRIM(RTRIM(CAST(MyFloat AS VARCHAR)))) AS MyFloatTrimmedLen,
    LEN(LTRIM(RTRIM(CAST(MyStr AS VARCHAR)))) AS MyStrTrimmedLen
FROM #temp;

ROLLBACK;

The results:

MyFloat    MyFloatLen  MyFloatDataLen MyStr      MyStrLen  MyStrDataLen
---------- ----------- -------------- ---------- --------- ------------
12345      5           8              12345      5         5 
123456789  12          8              123456789  9         9

(2 row(s) affected)

If we look at the execution plan, we see a hidden implicit conversion of applying the LEN() function to the float.  These implicit conversions can really hurt code execution: take a look at Grant Fritchey’s SQL Server Execution Plans, published by Red Gate.

Curious stuff.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s