【SQL/泛微】如何将某字段按指定字符进行分割替换？

• table 1 :
  id code
  1 001
  2 001,002
  3 001,002,003
• table 2:
  code name
  001 数学
  002 体育
  003 美术
• 要求结果
  id name
  1 数学
  2 数学，体育
  3 数学，体育，美术

--测试数据
with table1(id,code) as (
select 1,'001' union all
select 2,'001,002' union all
select 3,'001,002,003'),
table2(code,name) as(
select '001','数学' union all
select '002','体育' union all
select '003','美术')
 
--用charindex和for xml path实现批量替换的功能,适用于sql server 2005及以上版本
select table1.id,stuff((
    select ','+table2.name from table2
    where charindex(','+table2.code+',',','+table1.code+',')>0
    order by table2.code
    for xml path('')
    ),1,1,'') as name 
from table1

同理，将某货品对应的运营人员（多浏览按钮 ） 转换为中文提供给BI分析员。

SELECT
	uf_mrg_yeskulist.internalid,
	stuff(
		(
		SELECT
			',' + HrmResource.lastname 
		FROM
			HrmResource 
		WHERE
			charindex( ',' + CAST ( HrmResource.id AS VARCHAR ) + ',', ',' + CAST ( uf_mrg_yeskulist.custitem_sku_product_operator AS VARCHAR ) + ',' ) > 0 
		ORDER BY
			HrmResource.id FOR xml path ( '' ) 
		),
		1,
		1,
		'' 
	) AS yyname 
FROM
	uf_mrg_yeskulist

Sql中CHARINDEX用法

• 写SQL语句我们经常需要判断一个字符串中是否包含另一个字符串，但是SQL SERVER中并没有像C#提供了Contains函数，不过SQL SERVER中提供了一个叫CHAEINDX的函数，顾名思义就是找到字符（char）的位置（index），既然能够知道所在的位置，当然就可以判断是否包含在其中了。
• 通过CHARINDEX如果能够找到对应的字符串，则返回该字符串位置，否则返回0。
• 基本语法如下：
• CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
  • expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。
  • expressionToSearch ：用于被查找的字符串。
  •  start_location：开始查找的位置，为空时默认从第一位开始查找。

select charindex('test','this Test is Test')
#返回值6
select charindex('test','this Test is Test',7)
#返回值14

sql STUFF用法

• 1、作用
  • 删除指定长度的字符，并在指定的起点处插入另一组字符。
• 2、语法
  • STUFF ( character_expression , start , length ,character_expression )
• 3、示例
  • 以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串
    • SELECT STUFF('abcdef', 2, 3, 'ijklmn')
    • 结果集:aijklmnef
• 4、参数
  • character_expression一个字符数据表达式。character_expression 可以是常量、变量，也可以是字符列或二进制数据列。
  • start一个整数值，指定删除和插入的开始位置。如果 start 或 length 为负，则返回空字符串。如果 start 比第一个 character_expression长，则返回空字符串。start 可以是 bigint 类型。
  • length一个整数，指定要删除的字符数。如果 length 比第一个character_expression长，则最多删除到最后一个 character_expression 中的最后一个字符。length 可以是 bigint 类型。
• 5、返回类型
  • 如果 character_expression 是受支持的字符数据类型，则返回字符数据。如果 character_expression 是一个受支持的 binary 数据类型，则返回二进制数据。
• 6、备注
  • 如果结果值大于返回类型支持的最大值，则产生错误。