Validar registros duplicados de una columna, tomando varias posiciones de la cadena

[Libras]

espero que el siguiente codigo te sirva:

Código SQL:

CREATE TABLE #temp
(
code VARCHAR(100)
)
 
 
INSERT INTO #temp VALUES ('MARE30678965HP4')
INSERT INTO #temp VALUES ('MARE30678965HP5')
INSERT INTO #temp VALUES ('MARE30678965HP6')
INSERT INTO #temp VALUES ('MARE30678965HP7')
INSERT INTO #temp VALUES ('MARE30678965HP4')
INSERT INTO #temp VALUES ('MARE30678965H')
INSERT INTO #temp VALUES ('MARE3067896')
INSERT INTO #temp VALUES ('MARE306789')
INSERT INTO #temp VALUES ('MARE30678')
INSERT INTO #temp VALUES ('MARE30678')
 
SELECT primero, CASE WHEN m15>1 THEN 'si' ELSE 'no' END AS m15,
CASE WHEN m13>1 THEN 'si' ELSE 'no' END AS m13,
CASE WHEN m11>1 THEN 'si' ELSE 'no' END AS m11,
CASE WHEN m9>1 THEN 'si' ELSE 'no' END AS m9
FROM(
SELECT primero,SUM(m15) AS m15,SUM(m13) AS m13,SUM(m11) AS m11,SUM(m9) AS m9 FROM
(
SELECT primero,
CASE WHEN m15='si' THEN 1 ELSE 0 END AS m15,
CASE WHEN m13='si' THEN 1 ELSE 0 END AS m13,
CASE WHEN m11='si' THEN 1 ELSE 0 END AS m11,
CASE WHEN m9='si' THEN 1 ELSE 0 END AS m9
FROM(
SELECT  t1.code AS primero,t2.code AS segundo,
CASE WHEN (t1.code=t2.code AND len(t1.code)=len(t2.code)) AND (len(t1.code))=15 THEN 'Si' ELSE 'No' END AS m15,
CASE WHEN (t1.code=t2.code AND len(t1.code)=len(t2.code)) AND (len(t1.code))=13 THEN 'Si' ELSE 'No' END AS m13,
CASE WHEN (t1.code=t2.code AND len(t1.code)=len(t2.code)) AND (len(t1.code))=11 THEN 'Si' ELSE 'No' END AS m11,
CASE WHEN (t1.code=t2.code AND len(t1.code)=len(t2.code)) AND (len(t1.code))=9 THEN 'Si' ELSE 'No' END AS m9
 FROM #temp AS t1
, #temp AS t2
 ) AS t3
 ) AS total GROUP BY primero
 ) AS resultado
 

saludos!