Query that returns if the child inherited the surname of the father and / or mother

One good way to resolve this is to use 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 type SQL Server Database Project , after that you will have to add a new item of type SQL CLR C # User Defined Function , you will also need to add System.Core in references as we will use functions from it.

This will allow you to create a function in C # to be used internally by SQL Server, the function you will create to buy the surnames will be this 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 which will accept two strings as a parameter and return a boolean . Note that in my comparison I'm ignoring the Case Sensitive and the Sensitive Accent , plus I'm ignoring the first part of the name in the comparisons because you said you just want to buy the surnames of the people.

After doing this it will be necessary to give a Build in your project, so that it manages to create the DLL of it.

Going to the part of the Database now.

On the Sql Server it will be necessary 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 on your bank, select the bank 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 ClrTest is the name of the Assembly that will be created and the path from where is the DLL that was generated when you gave a build in your project.

After the Assembly is created we will create a Function in the database 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 hit use it in your query like this:

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

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

A working 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 this, just change your query '% query_with% function', like this:

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'm using the para utilizar a approach. It can be used in a controlled environment, but it is not much advised in production, because if you insert some illegal XML characters, it may explode.

On this site you can see several examples of how to implement other functions, with CLR , XML, Numerals, CTE, and Moden.

I also find it interesting to 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