Search for people with similar names

6

I'm developing a code to search for people's names intelligently using the SQL SERVER LIKE operator.

In names like Souza and Sousa, use brackets [] Ex.:

select * from pessoas where nome like 'joão sou[sz]a%';

The above example returns me:

id | nome
-----------------
10 | joão souza araujo
56 | joão sousa dos santos

In the case of the Souza example it was just a substitute for the letter, but in Vitor | Victor where the letter C is optional I could not get information on how to assemble the query. This is my question.

    
asked by anonymous 14.03.2018 / 16:11

1 answer

6

You can specify the end of the name inside the brackets.

declare @pessoas table
(
  id int,
  nome varchar(100)
)

insert into @pessoas values
(57,'Victor Sousa dos Santos'),
(57,'Vitor Souza Santos')

select * from @pessoas where nome like 'Vi[ctor]%';
--ou
select * from @pessoas where nome like 'Vi[ctor]% Sou[sz]%a [dos Santos]%';

Result.

  57    Victor Sousa dos Santos
  57    Vitor Souza Santos
    
14.03.2018 / 16:29