T-sql 2012 compare 2 strings with like statment issue

Transact-SQL
1

You cannot vote on your own post

0

I want to see if part of the field called teacherD equals the value in the field called lastname .

This is a sql server 2012 database.

Here is the sql

select * from test.dbo.table1
where substring(lastName,isnull(CHARINDEX(lower(lastName),lower([teacherD]) ),0),isnull(LEN(lastName),0) ) like '%lastName%'

The problem with this sql is the like statement listed here can not have wild card characters surrounding a database column as listed above.

This can you show me the sql to replace the like statement listed above or whatever you think the solution should be?

2

I might be misreading your requirements, but based on your first sentence quoted above, wouldn't the following be sufficient?

WHERE 
   teacherID like '%'+lastname+'%'

If the collation of the columns is different from the collation you want to use for comparison, force the collation to be whatever you want it to be. For example to compare using case insensitive, accent sensitive collation:

WHERE 
   teacherID COLLATE SQL_Latin1_General_CP1_CI_AS 
   like 
  '%'+lastname+'%' COLLATE SQL_Latin1_General_CP1_CI_AS