Link to home
Start Free TrialLog in
Avatar of Clifton Bardwell
Clifton BardwellFlag for United States of America

asked on

SQL Server 2005 - Comparing Fields' Contents

Let's see if I can explain this adequately...

I have two tables.  The first table has a student's full name (first, middle, last).  The second table has the name broken into it's parts (one field for FirstName, one for LastName, one for MiddleName).  Also in the second table is the StudentID number.

What I need to do is retrieve the StudentID from the second table by comparing the FirstName, MiddleName, and LastName of the second table to to the StudentName in the first table.

Here's an example of what I thought would work...
SELECT
    t1.StudentName,
    (   SELECT
            t2.StudentID
        FROM
            Table2 t2
        WHERE
            CONTAINS(t1.StudentName, t2.FirstName)
            AND
            CONTAINS(t1.StudentName, t2.MiddleName)
            AND
            CONTAINS(t1.StudentName, t2.LastName)
    ) AS StudentID
FROM
    Table1 t1

Open in new window


Running the script above gives me "error at line 8; Incorrect syntax near 't2'"

Any idea what I did wrong and, more importantly, how do I do what I want to do?
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
or this -  Note you may choose JOIN , and (or in where clause depending on your requirement).

SELECT
    *
FROM
Table2 t2 INNER JOIN 
Table1 t1 ON 
CHARINDEX(t1.StudentName,t2.FirstName,0) > 0
AND
CHARINDEX(t1.StudentName,t2.MiddleName,0) > 0
AND
CHARINDEX(t1.StudentName,t2.LastName,0) > 0
  

Open in new window

Avatar of Vitor Montalvão
CONTAINS should be used only if you have the Full Text Search service installed and running.
Using LIKE operator it can bring false positives (names that are like but then aren't what you really want). For an exact match use this query:
SELECT t2.StudentID, t1.StudentName
FROM Table1 t1
	INNER JOIN Table2 t2 ON t1.StudentName = t2.FirstName + ' ' + t2.MiddleName + ' ' + t2.LastName

Open in new window

Try these-

SELECT
    *
FROM
Table2 t2 INNER JOIN 
Table1 t1 ON 
CHARINDEX(t2.FirstName,t1.StudentName,0) > 0
AND
CHARINDEX(t2.LastName,t1.StudentName,0) > 0
AND
CHARINDEX(t2.StudentName,t1.StudentName,0) > 0

Open in new window

OR

SELECT
    *
	FROM
    Table2 t2 INNER JOIN 
	Table1 t1 ON 
            (t2.FirstName LIKE '%' + t1.StudentName + '%')
            AND
            (t2.MiddleName LIKE '%' + t1.StudentName + '%')
            AND
            (t2.LastName LIKE '%' + t1.StudentName + '%')

Open in new window


Hope it helps!
Avatar of Clifton Bardwell

ASKER

That worked.  

All this time and I never knew you could wrap a field with wildcards and use it in a LIKE!  :-)

Thanks much.
Glad to help..