[thelist] [SQL] determining if one column contains another?

[rudy]

> I figure anything I can do with Access I can do with SQL

not completely true

access can do a crosstab query without breaking a sweat

doing that in sql server is like getting a root canal


> but not necessarily vice versa.

yeah, the CASE structure is a lot easier to work with
than the ungainly IIF in access, especially if you start
nesting them

CHARINDEX and PATINDEX in sql server are nicer, too


>...  So I can't do something like,
> 
> SELECT Result, RevComment, Comment
> FROM tblResults 
> LEFT JOIN libComments 
> ON tblResults.RevComment LIKE %libComments.Comment%

pretty close  

try this --

    select Result, RevComment, Comment
      from tblResults 
    left outer
      join libComments 
         on charindex(libComments.Comment
                     ,tblResults.RevComment) > 0
                     
i tested it on the data you gave and it produced this --

    Result RevComment            Comment              
    ------ --------------------- -------------------- 
    99     Too hot.             Too hot.
    0      Too cold./Too hard.  Too cold.
    0      Too cold./Too hard.  Too hard.
    37     Just right.          NULL
    15     Too cold./Too soft.  Too cold.
    15     Too cold./Too soft.  Too soft.
    
    (6 row(s) affected)                
    

mmmm, i love sql server

rudy