t-sql 2012 match 2 strings

Question

t-sql 2012 match 2 strings

wendy elizabeth

SSCoach

Points: 16915
More actions
December 27, 2018 at 1:12 pm

#373370

In a sql server 2012 database, I want to find all the records where the lastname is not at least 'part' of the teacherD field.
The following sql works about 90% of the time:
        select lastname,teacherD
          from test.dbo.table145
   WHERE CHARINDEX(lower(lastName),lower([teacherD])) = 0
Different parts:
1. The problem is sometimes the teacherD field 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
2. If possible the user would also like to check if part of lastname is contained within teacherD.
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
Thus would you show me some sql on how to solve part #1 and part #2 if possible?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

I don't see special characters or a time. Your description doesn't quite make sense.

It would be better to see some DDL for the table and some DML to insert sample data that would help determine what cases you need to solve. Ideally I'd try to get a test that examines what you want written so that a variety of cases can be checked.

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

I hope the following explains what I am looking for:
For example. 'smith' from LastName and 'smith, darell' from teacherD should not be selected from the query above. 'Smith' is a subset of 'Smith, Darell'. Basically if 'Smith' is part of 'Smith, Darell', then the record should not be selected. The Logic above needs to not include Smith, as part of the selection. The comma is making that record get selected. Thus would you show me the sql to meet my requirements? Thanks for your help!

pietlinden SSC Guru Points: 63594 More actions · Answer 3

Got consumable data? (CREATE TABLE/INSERT scripts)??? Surely you've figured out how to do that by now. Unless you want just guesses instead of tested code.