clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601581 fiddles created (47994 in the last week).

CREATE TABLE Tbl_Personal ( Nombre VARCHAR(1000), Materno VARCHAR(1000), Paterno VARCHAR(1000) ) INSERT INTO Tbl_Personal (Nombre, Materno, Paterno) VALUES ('Luis Ortigoza', 'Marcela Morello', 'Juan manuel Funes'), ('Ernesto Guevara', 'Sandra Bullock', 'Luis Gilberto Enrique'), ('Juan Gilberto Funes', 'Natalie Portman', 'Samuel Fuller'), ('Juan maria Volonte', 'Gena Davies', 'Morgan Freeman') DECLARE @NombreBuscar VARCHAR(1000) SELECT @NombreBuscar = 'Funes Gilberto Juan' -- Armamos Tabla de palabras a buscar DECLARE @Palabras TABLE ( String VARCHAR(1000), NR INT ) INSERT INTO @Palabras (String, NR) SELECT String, ROW_NUMBER() OVER (ORDER BY A.String) AS 'NR' FROM (SELECT Split.a.value('.', 'VARCHAR(1000)') AS String FROM (SELECT CAST ('<M>' + REPLACE([Nombre], ' ', '</M><M>') + '</M>' AS XML) AS String FROM (SELECT @NombreBuscar AS 'Nombre') TableA ) AS A CROSS APPLY String.nodes ('/M') AS Split(a) ) A WHERE LEN(RTRIM(A.String)) > 0 SELECT T.Nombre, COUNT(DISTINCT P1.NR) AS 'CoincidenciasNombre', T.Materno, COUNT(DISTINCT P2.NR) AS 'CoincidenciasMaterno', T.Paterno, COUNT(DISTINCT P3.NR) AS 'CoincidenciasPaterno' FROM Tbl_Personal T LEFT JOIN @Palabras P1 ON T.Nombre LIKE '%' + P1.String + '%' LEFT JOIN @Palabras P2 ON T.Materno LIKE '%' + P2.String + '%' LEFT JOIN @Palabras P3 ON T.Paterno LIKE '%' + P3.String + '%' WHERE P1.String IS NOT NULL OR P2.String IS NOT NULL OR P3.String IS NOT NULL GROUP BY T.Nombre, T.Materno, T.Paterno DROP TABLE Tbl_Personal
Nombre CoincidenciasNombre Materno CoincidenciasMaterno Paterno CoincidenciasPaterno
Ernesto Guevara 0 Sandra Bullock 0 Luis Gilberto Enrique 1
Juan Gilberto Funes 3 Natalie Portman 0 Samuel Fuller 0
Juan maria Volonte 1 Gena Davies 0 Morgan Freeman 0
Luis Ortigoza 0 Marcela Morello 0 Juan manuel Funes 2
Warning: Null value is eliminated by an aggregate or other SET operation.
 hidden batch(es)