sql - T-SQL string concatenation `'string' + str(integer)` introduces extra space character
问题描述
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.
解决方案
问题是STR
:
返回从数字数据转换而来的字符数据。
STR ( float_expression [ , length [ , decimal ] ] )
是总长度。这包括小数点、符号、数字和空格。默认值为 10。
select REPLACE(str(Id), ' ', '-')
from Users
where DisplayName = 'Jon Skeet';
OUTPUT:
-----22656
我会简单地使用CONCAT
:
select CONCAT('https://stackoverflow.com/users/', id) AS link
from Users
where DisplayName = 'Jon Skeet';
推荐阅读
- sas - Proc 制表,在我的专栏中,我有超过 50 个变量,我怎样才能将其减少到只有 5 个?
- memory - Lua / Coroutines 如何测量标准函数占用多少内存
- ios - 在 Firebase 远程配置中设置最小间隔和设置 fetch withExpirationDuration 有什么区别?
- vue.js - 在 Vue v-if 中,如何检查对象数组中的对象是否具有指定键的特定值?
- amazon-web-services - AWS CDK 引导堆栈的用途和范围?
- php - AJAX 使用 if 条件重新加载数据
- javascript - 使用 axios 和 node js 同步调用
- r - 是否有另一种方法可以在 R 中不使用 for 循环来编写以下函数?
- r - 如何在 R 中将 Betafix 与 Coxphw 包一起使用?
- r - 读取数据框中的值并将其存储在向量中