declare @string varchar(30)
set @string = 'car, boat, van'

declare @lists table ( list varchar(30) )
insert @lists ( list )
select 'car, train, bike' union
select 'boat, truck, plane' union
select 'bike, plane, train'
;
with    z ( rn, list )
          as ( select   a.rn, a.list
               from     ( select    row_number() over ( order by list ) as rn,
                                    list
                          from      @lists
                        ) a
               union all
               select   a.rn,
                        left(z.list, len(z.list) - charindex(',', reverse(z.list)))
               from     ( select    row_number() over ( order by list ) as rn,
                                    list
                          from      @lists
                        ) a
               join     z on a.rn = z.rn
               where    charindex(',', z.list) > 0
             )
    select  z2.list
    from    z z1
    join    ( select    rn, max(list) as list
              from      z
              group by  rn
            ) z2 on z1.rn = z2.rn
    where   charindex(ltrim(right(z1.list, isnull(nullif(charindex(',', reverse(z1.list)) - 1, -1), len(z1.list)))), @string) > 0

quote:

---

Originally posted by helixpoint

I am getting these errors
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 12
'row_number' is not a recognized function name.
Msg 195, Level 15, State 10, Line 19
'row_number' is not a recognized function name.

---