Why ’10’ + 5 Returns 15: Mixing Strings and Numbers

When you mix strings and numbers with a plus sign, SQL Server decides by data type whether to add or to glue text together. That one decision turns ’10’ + 5 into 15 instead of 105.

The label that came out as a number

A teammate builds an invoice label: the text ’10’ followed by a line number 5. They expect 105 as text. The report shows 15, and nobody knows why until someone reads the query slowly.

The plus sign has two jobs in T-SQL. It adds numbers, and it joins text. SQL Server picks the job from the types on each side. Let me run both versions next to each other, plus CONCAT, which always joins.

SELECT '10' + 5 AS arithmetic_value,
       '10' + '5' AS concatenated_value,
       CONCAT('10', 5) AS explicit_text,
       '10' + CAST(NULL AS varchar(5)) AS null_with_plus,
       CONCAT('10', CAST(NULL AS varchar(5))) AS null_with_concat;

The first column is 15, the second and third are 105. Look at the last two columns too. A NULL on a plus makes the whole result NULL. CONCAT treats NULL as empty text, so you get 10.

Why int wins

When two different types meet, SQL Server converts the one with lower precedence to the one with higher precedence. Int outranks varchar. So the text ’10’ becomes a number, and the plus adds. Two varchar values stay varchar, and the plus glues them. This query shows the resulting types.

SELECT SQL_VARIANT_PROPERTY('10' + 5, 'BaseType') AS number_plus_text,
       SQL_VARIANT_PROPERTY('10' + '5', 'BaseType') AS text_plus_text;

You get int and varchar. The spelling of the literal, with quotes around it, hides this difference. That is why I do not trust a result that merely looks numeric.

The conversion also has a failure mode. If the text is not a number, SQL Server cannot add it.

SELECT 'a' + 5 AS invalid_arithmetic;

This stops with error 245: conversion failed when converting the varchar value ‘a’ to data type int. Better a loud error than a silent wrong answer, but it will page someone at night if bad data reaches the query.

Keys that look like numbers

Now a more expensive version of the same mistake. Suppose a text key column holds 10, 010 and A10. They are three different keys. The leading zero is part of the identity.

DROP TABLE IF EXISTS #Keys;

CREATE TABLE #Keys (Code varchar(20) PRIMARY KEY);
INSERT #Keys (Code) VALUES ('10'), ('010'), ('A10');

DECLARE @Code varchar(20) = '10';

SELECT Code
FROM #Keys
WHERE Code = @Code
ORDER BY Code;

A text parameter finds exactly one row: 10. Now let the client send the key as a number.

DECLARE @Number int = 10;

SELECT Code
FROM #Keys
WHERE Code = @Number
ORDER BY Code;

This time SQL Server converts every stored Code to int. Two keys match: 010 and 10, because both are the number 10. You asked for one key and got two. Then the scan reaches A10 and fails with error 245. A table with only digits in it would never fail. It would run quietly for months, return the wrong rows, and break on the first letter.

Using TRY_CONVERT to look, not to decide

TRY_CONVERT returns NULL instead of an error when a value does not convert. It is a good way to audit a column.

SELECT Code, TRY_CONVERT(int, Code) AS numeric_candidate
FROM #Keys
ORDER BY Code;

The rows 010 and 10 both become 10, and A10 becomes NULL. If you stored these keys as numbers, two different keys would merge into one. So match the client parameter type to the column, and keep identifiers as text when the characters matter.

DROP TABLE IF EXISTS #Keys;

Next time a result looks numeric, check which types the plus sign actually saw.

A plus sign is not a promise to concatenate, it is a decision made by data types.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.