THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Use the *1 trick to do math with two varchars


Someone had code like this on the tek-tips forum

DECLARE @v varchar(24)

SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) -SUBSTRING(@v,10,4)

If you run this code, you will get the following message

Server: Msg 403, Level 16, State 1, Line 4
Invalid operator for data type. Operator equals subtract, type equals varchar.

Instead of casting to integers you can also use this little trick. You basically multiply one of the values by 1

DECLARE @v varchar(24)

SELECT @v ='06029202400250029'

SELECT RIGHT(@v,4) *1 -SUBSTRING(@v,10,4)


Another example. This doesn't work

SELECT '2' - '1'

This does work

SELECT '2' * 1 - '1'

Published Wednesday, January 02, 2008 5:21 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter W. DeBetta said:

Why do this "trick" instead of being explicit about it?

SELECT CAST('2' as INT) - CAST('1' AS INT)

There is no performance difference yet your code is easier to read and in a sense self-commenting. What's worse is when you  are doing addition. If you forget the *1 then the result from SELECT '2' + '1' would be '21' instead of 3, since addition does not fail for 2 varchar values.

And although some might say this is apparent and would be caught, examine this function that is meant to do integer math on two varchars instead of varchar concatenation.

CREATE FUNCTION dbo.fnMathAddVarchar(@c1 VARCHAR(24), @c2 VARCHAR(24))

RETURNS INT

BEGIN

   RETURN @c1 + @c2

END

GO

SELECT dbo.fnMathAddVarchar('2', '1')

This would return the integer 21, instead of the expected value of 3. But, since no exception would occur, the developer might assume it is working fine because that person would likely not check the answer, but rather just check to see it doesn't fail.

This function should have been written:

CREATE FUNCTION dbo.fnMathAddVarchar(@c1 VARCHAR(24), @c2 VARCHAR(24))

RETURNS INT

BEGIN

   RETURN CAST(@c1 AS INT) + CAST(@c2 AS INT)

END

GO

--Peter

January 2, 2008 5:34 PM
 

Bart Czernicki said:

Neat trick...reminds me of the "select 3/2" question u had on ur one blog post a couple weeks ago.

I do have to agree with Peter though that I would rather cast it.

January 2, 2008 5:54 PM
 

Denis Gobo said:

Peter,

true, true, true if you put the code in a proc I would also cast it. I use it mostly for ad-hoc sql

January 2, 2008 6:19 PM
 

AaronBertrand said:

You know, I'm kind of torn on the cast or not thing.  In the case of string -> number, I prefer it, as the behavior is problematic in this case... you can easily pass junk into a string and then fail when trying to multiply.  On the other hand, I am a huge fan of using shorter syntax when going the other way, e.g. appending a number to a string:

SET @msg = 'Backup failed ' + RTRIM(@RetryCount) + ' times.';

While it's not entirely clear to the casual observer why there is an RTRIM() there, I personally find it much easier to read than CONVERT() or CAST().  Plus, I have found that a lot of people who convert *to* a char or varchar don't bother to specify the length, or specify something that leads to failure later (e.g. VARCHAR(4), which works until the integer value hits 10000).

January 3, 2008 7:13 AM
 

Tom Powell said:

Denis,

I'm afraid I have to agree with Peter, why make it hard for the next guy?  I've come into a lot of different shops and I hate it when people get cute with their code.  To be honest, I cringe at some of the "cuteness" I've left in my wake.  But I'm over that now... :-)

Tom

January 3, 2008 8:27 AM
 

jerryhung said:

we all have cute/lazy tricks

PRINT '# of rows updated = ' + STR(@@ROWCOUNT)

CAST(@int AS VARCHAR)

January 3, 2008 9:27 AM
 

Peter W. DeBetta said:

In general, relying on implicit conversions is a bad thing, whether you are working in T-SQL, PL/SQL, VB, C#, and so on. The rules of implicit conversion are bound only to the version of the language with which you are working, so things could change from version to version; whereas you are less likely to run into trouble if you explicitly cast every time.

Sure, there are exceptions that everyone uses, such as relying on the implicit conversion from smallint to int. When the conversion is just in increase in size or precision, implicit conversions have always been acceptable. But as a rule, I always explicitly convert/cast, and to me at least, it always makes the code more readable.

January 3, 2008 11:14 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement