insert into demo
select 3,'aa','1001' union all 
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类1','4001' union all
select 7, '分类2','5001' union all
select 8,'分类','2002'

create table demo (id int,name nvarchar(32),code varchar(32))
insert into demo
select 3,'aa','1001' union all 
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类1','4001' union all
select 7, '分类2','5001' union all
select 8,'分类','2002'

declare  @str nvarchar(32)=N'分类,2'
declare @str1 nvarchar(32),@str2 nvarchar(32)
select @str1=PARSENAME(REPLACE(@str,',','.'),2),@str2=PARSENAME(REPLACE(@str,',','.'),1)

select * from demo where (name like '%'+@str1+'%' or name like '%'+@str2+'%' or code like '%'+@str1+'%' or code like '%'+@str2+'%')

/*
id    name    code
4    bb    2001
6    分类1    4001
7    分类2    5001
8    分类    2002
*/

------解决方案--------------------

探讨

另换帖子了，问题不是简单的一说，试过几种方法，split charindex 貌似都达不到效果
测试数据

SQL code


insert into demo
select 3,'aa','1001' union all 
select 4,'bb','2001' union all
select 5, 'cc','3001' union all
select 6, '分类……


------解决方案--------------------
那帖子树锅已修改。LIKE 方向已经改了.