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.
