Use the *1 trick to do math with two varchar values
From Wiki
Let's say you have to do match with two values from a string/varchar. Usually you will have to cast to an integer in order to do that, run the code below to see what I mean
- 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'
Contributed by: --SQLDenis 03:01, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
