Computing value in select statement t-sql

Question

I am trying to compute a value in select statement itself but surprisingly it results in 0.

SELECT Top(1) Name, 
LEN(Name) AS Equals, 
Abs(LEN('Johny') - LEN(Name)) AS NotEquals, 
LEN(Name)/(Abs(LEN('Johny') - LEN(Name)) + LEN(Name)) As Match
FROM Demo
WHERE Name = LEFT( 'Johny' , LEN(Name) ) 
ORDER BY LEN(Name) DESC

Output:

Name    Equals  NotEquals   Match
John    4           1         0

Why exactly is value of match field 0 in output?

Because you do integer devision which cuts off the digits after the comma. And a result smaller than 1 is 0. — juergen d, Feb 04 '15 at 11:34
possible duplicate of [how to get a float result by dividing two integer values?](http://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values) — GSerg, Feb 04 '15 at 11:35

score 2 · Answer 1 · answered Feb 04 '15 at 11:35

2

Because your calculation uses integers:

4/5 = 0

All behind the comma is removed

answered Feb 04 '15 at 11:35

Peter

27,590
8
64
84

score 2 · Answer 2 · answered Feb 04 '15 at 11:35

2

It's this:

LEN(Name)/(Abs(LEN('Johny') - LEN(Name)) + LEN(Name)) As Match

4 / ((5 - 4) + 4)

4/ (1 + 4)

4 / 5

= 0

answered Feb 04 '15 at 11:35

Christian Barron

2,695
1
14
22

score 2 · Accepted Answer · answered Feb 04 '15 at 11:35

you are trying to divide integer values where divisor > dividend

try cast one value to float

SELECT Top(1) Name, 
LEN(Name) AS Equals, 
Abs(LEN('Johny') - LEN(Name)) AS NotEquals, 
cast(LEN(Name) as float)/(Abs(LEN('Johny') - LEN(Name)) + LEN(Name)) As Match
FROM Demo
WHERE Name = LEFT( 'Johny' , LEN(Name) ) 
ORDER BY LEN(Name) DESC

Computing value in select statement t-sql

3 Answers3