T-SQL string concatenation `'string' + str(integer)` introduces extra space character

Question

Sorry for the somewhat trivial question, but why does

select ('https://stackoverflow.com/users/' + str(Id)) as Link
from Users
where DisplayName = 'Jon Skeet';

when entered into the Data Explorer return

https://stackoverflow.com/users/ 22656

instead of

https://stackoverflow.com/users/22656

?

According to Microsoft's documentation on T-SQL's + operator,

'book' + 'case'

should give

'bookcase'

not

'book case'

and according to the Data Explorer documentation, the SQL-flavor used in the Stack Exchange Data Explorer is indeed T-SQL.

Some additional experiments:

select str(42);

returns "42", without extra spaces (see EDIT below).

select ('foo' + 'bar');

returns "foobar", also without spaces.

select ('foo' + '42');

returns "foo42", so it doesn't treat digits specially or anything like that.

To me, it looks as if basic semantic compositionality principle is violated. What am I missing here?

EDIT The problem turned out to be the wrong assumption that

select str(42);

returns "42". It actually returns

"        42"

but the spaces are ignored by the browser-based GUI.

Here is another example that demonstrates the problem more clearly:

select 'foo' + str('42');

seems to return

"foo 42"

but it actually returns

"foo        42"

as can be seen in this query:

select LEN('foo' + str('42'));

which returns 13 (not 5 and also not 6). Many thanks @lad2025 for pointing this out.

So, it was mostly an "optical illusion" caused by a somewhat inaccurate representation of string-results in the browser.

I'm going to guess ID isn't numeric. What datatype is ID? varchar? if so ID contains the extra space. — xQbert
– xQbert, Commented May 24, 2018 at 13:29
The documnentation for str says: "A short float_expression is right-justified in the specified length, and a long float_expression is truncated to the specified number of decimal places. For example, STR(12,10) yields the result of 12. This is right-justified in the result set." — user47589
– user47589, Commented May 24, 2018 at 13:30
(Spaces invisible in comment inline-code, so replaced spaces by -) Ok, so, the select str(42); returns "---------42" instead of "42", but the spaces are ignored by the browser-based GUI. Here is another example that demonstrates the problem more clearly: select 'foo' + str('42'); seems to return "foo-42", but it actually returns "foo--------42", as can be seen in this query: select LEN('foo' + str('42')); which returns 13 (not 5 and also not 6). Many thanks @lad2025 for pointing this out. — Andrey Tyukin
– Andrey Tyukin, Commented May 24, 2018 at 13:44

Yogesh Sharma · Accepted Answer · 2018-05-24 14:00:49Z

9

The problem is STR:

Returns character data converted from numeric data.

STR ( float_expression [ , length [ , decimal ] ] )

Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

select REPLACE(str(Id), ' ', '-')
from Users
where DisplayName = 'Jon Skeet';

OUTPUT:
-----22656

I would simply use CONCAT:

select CONCAT('https://stackoverflow.com/users/', id) AS link
from Users
where DisplayName = 'Jon Skeet';

See Demo

edited May 24, 2018 at 14:00

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

answered May 24, 2018 at 13:29

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andrey Tyukin Over a year ago

Is then the "42" output for str(42) merely an artifact of the browser-based GUI that ignores whitespace?

xQbert Over a year ago

concat applies implicit conversion of the int datatype to string then right?

Lukasz Szozda Over a year ago

@xQbert Yes, the is implicit cast

Lukasz Szozda Over a year ago

@AndreyTyukin SELECT LEN(str(42)) -- 10 Demo

Andrey Tyukin Over a year ago

Yes, and also select LEN('foo' + str('42')); returns 13, not 5 or 6. It's just the browser-based GUI that hides the extra spaces. Thank you, that explains it.

Collectives™ on Stack Overflow

T-SQL string concatenation `'string' + str(integer)` introduces extra space character

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related