CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user1816979
user1816979

Reputation: 531

T-SQL 2012 charindex comparing several strings does not match

In a SQL Server 2012 database, I want to find all the records where the lastname is not at least 'part' of the teacherD column.

The following SQL works about 90% of the time: 

SELECT lastname, teacherD
FROM test.dbo.table145    
WHERE CHARINDEX(LOWER(lastName), LOWER([teacherD])) = 0

The problem is sometimes the teacherD column has special characters right next to the time. The above SQL thinks there is a difference.

Here are examples of where there are problems

lastname       teacherD  
--------------------------------
smith          smith, darell
smith          smith-bright joan

If possible the user would also like to check if part of lastname is contained within teacherID. Here are some examples of what I am referring to:

lastname       teacherD
------------------------------------
corbly-tip     corbly, annette b
baker-hage     cubs - bakerhage - 8c
king grie      king, bev

Please could you show me some SQL on how to solve part #1 and part #2?

Upvotes: 0

Views: 101

Answers (1)

Hogan
Hogan

Reputation: 70513

To solve the first part do this:

SELECT lastname, teacherD
FROM test.dbo.table145    
WHERE CHARINDEX(LOWER(lastName), LOWER('--'||[teacherD])) = 0

The problem you had what charindex was returning 0 because it was at location 0, so we add something to the start of the string that won't match so 0 returned will always be a fail.

I don't know what you want to do for the 2nd part.

Upvotes: 1

Related Questions