查询包含字符的SQL语句？
各大师，请教下，有下面两张表
表1：
CEL COVTYEP NUM
AAA outdoor 53
BBB outdoor 516
CCC outdoor 740
DDD indoor 50
EEE outdoor 44
FFF indoor 746
表2：
CELNUM FLAG STRNUM
1 outdoor 44,47,50,53，56
2 indoor 44, 47, 50, 53
3 outdoor 512, 514, 516, 518
4 outdoor 738, 740, 742, 744, 746
5 indoor 736, 740, 742, 744
如上表两张表：如何实现SQL语句查询出CEL以及CEL字段对应的CELNUM值。如查询AAA的CELLNUM,则从NUM里53对应到STRNUM字段，若包含53且FLAG=COVTYPE,则显示出该AAA值和CELNUM的1值。
[最优解释]
if object_id('[表1]') is not null drop table [表1]
go
create table [表1] (CEL nvarchar(6),COVTYEP nvarchar(14),NUM int)
insert into [表1]
select 'AAA','outdoor',53 union all
select 'BBB','outdoor',516 union all
select 'CCC','outdoor',740 union all
select 'DDD','indoor',50 union all
select 'EEE','outdoor',44 union all
select 'FFF','indoor',746
if object_id('[表2]') is not null drop table [表2]
go
create table [表2] (CELNUM int,FLAG nvarchar(14),STRNUM VARCHAR(30))
insert into [表2]
select 1,'outdoor','44,47,50,53,56' union all
select 2,'indoor','44,47,50,53' union all
select 3,'outdoor','512,514,516,518' union all
select 4,'outdoor','738,740,742,744,746' union all
select 5,'indoor','736,740,742,744'
select * from [表1]
select * from [表2]
SELECT a.cel,b.celnum
FROM [表2] b
INNER JOIN [表1] a ON a.covtyep = b.flag AND CHARINDEX(CONVERT(VARCHAR,a.num),b.strnum) = 1
/*
celcelnum
EEE1*/
--更正一下
SELECT a.cel,b.celnum
FROM [表2] b
INNER JOIN [表1] a ON a.covtyep = b.flag AND CHARINDEX(CONVERT(VARCHAR,a.num),b.strnum) >0
/*
celcelnum
AAA1
BBB3
CCC4
DDD2
EEE1
EEE4*/
select a.CEL,b.CELNUM from t1 as a
Left join t2 as b on Charindex(ltrim(a.NUM),b.STRNUM)>0 and a.COVTYEP=b.FLAG)
select 1,'outdoor','44,47,50,53,56' union all
select 2,'indoor','44,47,50,53' union all
select 3,'outdoor','512,514,516,518' union all
select 4,'outdoor','738,740,742,744,746' union all
select 5,'indoor','736,740,742,744'