Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
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
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.