*关于字符串查询语句*解决方法

***关于字符串查询语句***
  select '单杠挂衣,双杠挂衣' as id  into a
  union all
  select  '单杠挂衣,指定挂衣' 
  .....
....

  
     select 'OU2015012800001' as jobno,'双杠挂衣,单杠挂衣' as id into b union all
  select 'OU2015012800002','通试箱,指定提单箱' 
   ...
...

 

 想得到结果jobno:OU2015012800001
 
 A表ID与B表ID对比，个数相等，字符相同的JOBNO
 
------解决思路----------------------

if OBJECT_ID('Tempdb..#a') is not null drop table #a
if OBJECT_ID('Tempdb..#b') is not null drop table #b
create table #a(
aID  nvarchar(100) null
)
create table #b(
jobno nvarchar(100) null,
bID  nvarchar(100) null
)
insert into #a 
select '单杠挂衣,双杠挂衣' union all
select '单杠挂衣,指定挂衣'  
 insert into #b 
select 'OU2015012800001','双杠挂衣,单杠挂衣' union all
select 'OU2015012800002','通试箱,指定提单箱'  

select b.jobno,a.aID,b.bID from #a a join #b b
on a.aID=b.bID 
or (PARSENAME(replace(a.aID,',','.'),1)=PARSENAME(replace(b.bID,',','.'),2)  
    and PARSENAME(replace(a.aID,',','.'),2)=PARSENAME(replace(b.bID,',','.'),1)  
) 
---------------------------------------
(2 行受影响)

(2 行受影响)
jobno                                                                                                aID                                                                                                  bID
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
OU2015012800001                                                                                      单杠挂衣,双杠挂衣                                                                                            双杠挂衣,单杠挂衣

(1 行受影响)