SQLServer将查询内容拼接成字符串输出（超全面！）

一、背景

    当我想通过 select 语句将查询内容拼接成字符串输出时出现这种情况。

select U_ID+U_NAME from MES_USER

消息 245，级别 16，状态 1，第 1 行
在将 varchar 值 '小白' 转换成数据类型 int 时失败。

用+号直接拼接的肯定不行，那是在编程语言中的做法。

二、问题解决

  （1）问题重现

select 1+'--Test--'
--> 在将 varchar 值 '--Test--' 转换成数据类型 int 时失败。

（2）解决方案

方案一：cast 函数

select CAST(1 as varchar)+ '--Test--'
--> '1--Test--'

方案二：convert 函数 

     在 sql 语句中用 cast 或 convert 函数进行强转，转型成功后即可拼接

select CONVERT(varchar,1)+ '--Test--'
--> '1--Test--'

方案三：RTRIM（）/ LTRIM（）字符串函数

select RTRIM(U_ID)+U_NAME from MES_USER
select LTRIM(U_ID)+U_NAME from MES_USER

    这里使用了SQL SERVER字符串函数LTRIM() / RTRIM(),分别为去除字符串左边多余的空格函数和去除字符串右边多余的空格函数，此为一对函数。在去掉空格时会将其中类型强转成字符串类型。

方案四：（重点来了！）

select (STUFF((select  ','+ RTRIM(U_ID) + U_NAME from MES_USER for xml path ('')),1,1,''))

这个 sql 是将所有查询内容拼接成一整串字符串，并用逗号隔开，稍微复杂了一点点，涉及到三个函数，不过不急，待我慢慢道来……

函数一：字符串函数LTRIM()/RTRIM()

    这个上面说过了，不再复述。

函数二：for xml path(param)--将查询结果以xml格式输出

（1）当 path 后面没有参数时，每行数据被默认 <row> 标签包裹，每行列数据被被其列名标签包裹。例：

select U_ID,U_NAME  from MES_USER for xml path

--结果：
<row>
  <U_ID>1</U_ID>
  <U_NAME>小白</U_NAME>
</row>
<row>
  <U_ID>2</U_ID>
  <U_NAME>小黑</U_NAME>
</row>
<row>
  <U_ID>3</U_ID>
  <U_NAME>大黑</U_NAME>
</row>

（2） - 每行数据最外面包裹的标签由path的参数控制（没有参数时，默认为row），所以要去掉则可以直接给一个空字符串作为path的参数。

- 每行列数据所被直接包裹的标签由查询结果的列名控制，要去掉只需要保证查询出来的列没有列名就可以了（保证没有列名最简单的方法是给每个字段加一个空字符串）。例：

select U_ID+'',U_NAME+''  from MES_USER for xml path('')

--结果
1小白2小黑3大黑

函数三：stuff函数

格式：stuff(character_expression, start, length, replaceWith_expression)

删除原字符串（character_expression）指定起始位置（start）指定长度（length）的字符，并在该位置插入代替的字符（replaceWith_expression）

select (STUFF((select  ','+ RTRIM(U_ID) + U_NAME from MES_USER for xml path ('')),1,1,''))

这里的意思就是将第一个逗号用空字符替换掉。

小提示：这里查出来的结果可以放到另一个 select 的条件 IN 里面，特别方便，练熟之后就不用老是一个个去添加逗号了。

 

三、补充

  将小数返回成百分数的格式输出。

（1）不保留小数位

select cast(cast(0.888876*100 as numeric(3,0)) as varchar(10)) +'%'
--> 89%

（2）保留小数位

select cast(cast(0.888876*100 as numeric(5,2)) as varchar(10)) +'%'
--> 88.89%