SQL报错：varchar值'%,'转int失败，求主从表查询解决方案

原始表结构与修正后的插入数据 #

首先，原始插入语句存在语法错误（每个VALUES组的字段数与表结构不匹配），先修正为正确的插入语句：

create table dbo.GMaster
(
    CourseId char(2),
    CourseName char(3)
);

create table dbo.TMaster
(
    ROLLNO char(5),
    NAME varchar(10),
    ADDRESS varchar(20),
    Course varchar(100)
);

insert into dbo.GMaster 
values ('-1', 'All'),
       ('0',  ''),
       ('1',  'AD'),
       ('3',  'Unassigned'),
       ('4',  'C'),
       ('7',  'PS');

insert into dbo.TMaster 
values ('1', 'RAM', 'RAM ADDRESS', '0'),                      
       ('3', 'HARI', 'HARI ADDRESS', ',1,4'),                         
       ('4', 'JEFF', 'JEFF ADDRESS', '-1'),                    
       ('6', 'DAISY', 'DAISY ADDRESS', ',7'),
       ('8', 'Flow', 'Chart', ',3');

期望输出 #

报错信息 #

Conversion failed when converting the varchar value '%,' to data type int

尝试的SQL代码 #

select 
    SM.ROLLNO,
    SM.NAME, 
    SM.ADDRESS,
    (select 
         ',' + CM.CourseName
     from 
         dbo.GMaster as CM
     where 
         ',' + SM.Course + ',' like '%,' + CM.CourseId + ',%'
     for xml path(''), type).value('substring(text()[1], 2)', 'varchar(max)') as Course
from 
    dbo.[TMaster] as SM;

问题分析与解决方案 #

报错原因 #

报错源于隐式数据类型转换：CM.CourseId是char(2)类型，但SQL Server在执行like比较时，会尝试将'%,'这类字符串转换为数值类型（因为SM.Course中包含数字内容），导致转换失败。同时原始SQL未处理CourseId='-1'的全量匹配逻辑，也未按期望格式添加单引号、处理空值。

修正后的SQL代码 #

select 
    SM.ROLLNO,
    SM.NAME, 
    SM.ADDRESS,
    case 
        -- 处理Course为'0'的情况，返回指定空值文本
        when SM.Course = '0' then '空值'
        else
            (select 
                 ',' + '''' + CM.CourseName + ''''
             from 
                 dbo.GMaster as CM
             -- 当Course为'-1'时返回所有课程，否则匹配对应CourseId
             where 
                 SM.Course = '-1' 
                 or ',' + SM.Course + ',' like '%,' + CM.CourseId + ',%'
             -- 按CourseId排序保证输出顺序与期望一致
             order by CM.CourseId
             for xml path(''), type).value('substring(text()[1], 2)', 'varchar(max)')
    end as Course
from 
    dbo.[TMaster] as SM;

代码说明 #

1. 解决隐式转换：通过条件分支单独处理SM.Course='-1'的全量匹配场景，避免模糊匹配时触发不必要的隐式类型转换。
2. 匹配期望格式：
   • 对Course='0'的记录直接返回"空值"
   • 用转义单引号（''''）给每个课程名称包裹单引号
   • 全量匹配时返回GMaster中所有课程名称
3. 排序保证顺序：添加order by CM.CourseId，确保输出的课程顺序与期望一致。

内容的提问来源于stack exchange，提问作者Test