Query that returns if the child has inherited the parent’s and/or mother’s surname

Question

I have a scenario that I need to sort my records where:

The child inherited the surname of the father and/or his mother?

Note in the image that if one of the child’s surnames exists in the parent’s name the field PAI? gets true. Similarly to the field MAE?.

Scenario building

CREATE TABLE USUARIOS
    (CODIGO INT IDENTITY,
     NOME VARCHAR(255),
     MAE VARCHAR(255),
     PAI VARCHAR(255)
    )
CREATE CLUSTERED INDEX PK_CODIGO ON USUARIOS (CODIGO);
CREATE INDEX NOME ON USUARIOS (NOME, CODIGO);
CREATE INDEX MAE ON USUARIOS (MAE, CODIGO) INCLUDE(NOME);
CREATE INDEX PAI ON USUARIOS (PAI, CODIGO) INCLUDE(NOME);

GO

INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('ADRIANA ARAUJO DIAS','JOAO WILSON ARAUJO','JOSELIA PEREIRA ARAUJO')
INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('NICOLAS PEDRO SILVA','SERGIO LUIZ SILVA JUNIOR','THAIS BATISTA DOS SANTOS SILVA')
INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('CARLOS JOSE DOS SANTOS ANJO','ACACIO APARECIDO ANJO DA SILVA','JULIANA ARAUJO DOS SANTOS ANJO')
INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('NICOLAY RIBEIRO DANTAS','RICARDO VICENTE RIBEIRO','JOZEILDA LUIS ENCARNACAO')
INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('MARIA DA SILVA MATTOS','FRANKLIN DE OLIVEIRA','BRUNA CATARINA DA SILVA MATTOS')
INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('CREUSA MARIA MORAES','GELSON DO ESPIRITO SANTO','FATIMA APARECIDA DOS SANTOS')
INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('DAVID BRADAO BORGES','EMERSON DOS SANTOS BORGES','DAYANE EVANGELISTA ACACIO BORGES')
INSERT INTO USUARIOS (NOME, MAE, PAI) VALUES('LUIZ ADAO SANTOS DA SILVA','WESLEY GONCALVES SILVA','IZABEL BRANDAO DA SILVA')

GO

SELECT CODIGO, NOME, PAI, MAE,
        (SELECT COUNT(*) FROM USUARIOS T1
         WHERE T1.CODIGO = TAB.CODIGO AND
               CHARINDEX(SUBSTRING(NOME, CHARINDEX(' ', NOME)+1, CHARINDEX(' ', NOME, CHARINDEX(' ', NOME)+1) - CHARINDEX(' ', NOME)-1), PAI) > 0  --FUNCIONA PARA O PRIMEIRO SOBRENOME (PAI)
         ) AS [PAI?],
        (SELECT COUNT(*) FROM USUARIOS T1
         WHERE T1.CODIGO = TAB.CODIGO AND
               CHARINDEX(SUBSTRING(NOME, CHARINDEX(' ', NOME)+1, CHARINDEX(' ', NOME, CHARINDEX(' ', NOME)+1) - CHARINDEX(' ', NOME)-1), MAE) > 0  --FUNCIONA PARA O PRIMEIRO SOBRENOME (MÃE)
        ) AS [MAE?]
FROM USUARIOS TAB

How far I’ve come, with the above query, I can at least verify with the first surname, but, how to do this query to check all surnames?

Note: The names I presented are fictitious. Do not consider if they happen to coincide with real people.

Answer 1

A good way to solve this is by using the CLR, with it we will create a function in . Net to run directly in the database.

First, we need to create a new project in Visual Studio of the type SQL Server Database Project, after that you will have to add a new item of the type SQL CLR C# User Defined Function, will also be necessary to add in the System Core., because we will use her functions.

This will allow you to create a C# function to be used internally by SQL Server, the function you will create to buy the last names will be that way:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean ComparaSobrenome(string nome1, string nome2)
    {
        if (nome1 == null || nome2 == null)
            return new SqlBoolean(false);

        //Ignora o priemiro nome da pessoa na comparação, caso queira incluir ele retirar .Skip(1).ToArray()
        var nome1Array = nome1.Split(' ').Skip(1).ToArray();
        var nome2Array = nome2.Split(' ').Skip(1).ToArray();

        if (nome1Array.Length == 0 || nome2Array.Length == 0)
            return new SqlBoolean(false);

        foreach (var n1 in nome1Array)
        {
            foreach (var n2 in nome2Array)
            {
                if (ContainsInsensitive(n1, n2))
                {
                    return new SqlBoolean(true);
                }
            }
        }

        return new SqlBoolean(false);
    }

    public static bool ContainsInsensitive(string source, string search)
    {
        return string.Compare(source, search, CultureInfo.GetCultureInfo("pt-BR"), CompareOptions.IgnoreNonSpace | CompareOptions.IgnoreCase) == 0;
    }
}

This will create a function called ComparaSobrenome that will accept two strings as parameter and return a Boolean. Note that in my comparison I am ignoring the Case Sensitive and the Accent Sensitive, also I am ignoring the first part of the name in the comparisons, because you informed that only want to buy the surnames of people.

After having done this it will be necessary to give a Build on your project, for it to generate create the DLL from it.

Going to the Database Part now.

In Sql Server you will need to run the following commands to enable the use of CLR, which by default is disabled.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

After that you will need to install the DLL in your database, select the database you want your function to be created and run the following command

Create Assembly ClrTeste from 'C:\ClrTeste\ClrTeste\bin\Debug\ClrTeste.dll' with Permission_set = SAFE
GO

Since Clrteste is the name of the Assembly that will be created and the path from is where is the DLL that was generated when you build in your project.

After the creation of Assembly we will create a Function in the bank that will call this Assembly.

Create Function ComparaSobrenome(@Nome nvarchar(max), @Nome2 nvarchar(max) )
RETURNS bit
WITH EXECUTE AS CALLER
AS
    EXTERNAL NAME ClrTeste.UserDefinedFunctions.ComparaSobrenome;
GO 

After that tap uses it in your query that way:

SELECT NOME, MAE, PAI, dbo.ComparaSobrenome(NOME, MAE) AS SobrenomeMae, dbo.ComparaSobrenome(NOME, PAI) AS SobrenomePai
FROM USUARIOS

Answer 2

You can create a function to perform the split of the data and then just do a join simple.

A functional example would be this:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

After that, just change your query ``para utilizar aFunction`, in this way:

SELECT codigo, nome, pai, mae, 
(SELECT CASE WHEN EXISTS 
(
  SELECT 1 
    FROM dbo.SplitStrings_XML(nome, ' ') AS a
    INNER JOIN dbo.SplitStrings_XML(pai, ' ') AS b
    ON a.Item = b.Item
)
THEN 1 ELSE 0 END) as 'Pai?',
(SELECT CASE WHEN EXISTS 
(
  SELECT 1 
    FROM dbo.SplitStrings_XML(nome, ' ') AS a
    INNER JOIN dbo.SplitStrings_XML(mae, ' ') AS b
    ON a.Item = b.Item
)
THEN 1 ELSE 0 END) as 'Mae?'
FROM USUARIOS

See the functional example in Sqlfiddle.

Note that I am using the approach with XML. It can be used in a controlled environment, but is not very recommended in production, because if you insert some illegal XML characters, it can explode.

On this website you can see several examples of how to implement other functions, with CLR, XML, Numéros, CTE and Moden.

I find it interesting also you look at these questions:

• How to compare two strings with same delimiter in SQL Server
• In SQL Server, how to create while loop in select