Search for people with similar names

Asked

Viewed 200 times

6

I am developing code to search for people names intelligently using SQL SERVER LIKE operator.

In names like Souza and Sousa just 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 example of souza|souza it was only to replace the letter, but in Vitor|Victor in which the letter C is optional I could not get information on how to assemble the query. This is my doubt.

sql sql-server
  • 1

    Possible duplicate of How to make a phonetic algorithm for Brazilian Portuguese?

  • 1

    In Oracle I use UTL_MATCH EDIT_DISTANCE and JARO_WINKLER for sql server a quick search returned https://stackoverflow.com/questions/33882620/jaro-winkler-in-sql-server

  • @Sorack didn’t know the phonetic algorithm. I’m going to look it up

  • Has an answer that has the algorithm for the MySQL. I have somewhere the same example for SQL Server, but I have to look...

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.