CODEWITHSUNDEEP
sqlsql-server-2008
sureoy
sureoy

Reputation: 1

How to search name, (OPTIONAL) second name, surname on SQL?

I am not usually an SQL person but need to find matching name/surnames from a list of people on MSSql. I am trying to find similar names from TABLENAME to match the list of people that I have. The line looks like this:

Select * 
from TABLENAME(nolock)
Where SomeRule='04' and RTRIM(Name) +' ' + RTRIM(SecondName) +' '+RTRIM(Surname) in (THE LIST OF PEOPLE HERE)

But this method only gives me the match of people with second names. If someone does not have a second name but their name+surname match, it does not show up. I want to see people who have 100% match of name-second name-surname OR name-surname (if they don't have second name).
Thank you guys in advance

Upvotes: 0

Views: 386

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Let's look at some rows:

Name SecondName Surname Remark Your concatenation
'John' 'Thomas' 'Smith' Person with middle name 'John Thomas Smith'
'John' '' 'Smith' Person that has no middle name 'John  Smith'
'John' null 'Smith' Person with an unknown middle name null

The person without a middle name cannot be found because of two blanks separating first and last name in your created string. The person of whom we don't know the middle name cannot be found because the concatenated string is null.

You can use CASE WHEN to check this and react accordingly:

select * 
from tablename
where somerule = '04'
and
  rtrim(name) + ' ' + 
  case when rtrim(secondname) is null then ''
       when rtrim(secondname) = '' then ''
       else rtrim(secondname) + ' '
  end +
  rtrim(surname) in (the list of people here)

Upvotes: 0

Serg
Serg

Reputation: 22811

Provided list of people is a table

Select distinct t.* 
from TABLENAME t
join [THE LIST OF PEOPLE HERE] lp on SomeRule='04' 
     and RTRIM(t.Name) = lp.Name 
     and RTRIM(t.Surname) = lp.Surname 
     and (t.SecondName is null and pl.SecondName is null or RTRIM(t.SecondName) = lp.SecondName)

Upvotes: 1

Related Questions