Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (id, name, contact) AS
SELECT 1, 'A', 3265489 FROM DUAL UNION ALL
SELECT 1, 'A', NULL    FROM DUAL UNION ALL
SELECT 1, 'A', 5645564 FROM DUAL UNION ALL
SELECT 2, 'B', NULL    FROM DUAL;
4 rows affected

SELECT id,
       MAX(name) AS name
FROM   table_name
GROUP BY id
HAVING COUNT(contact) = 0;


ID
NAME


2
B


SELECT id,
       MAX(name) AS name
FROM   table_name
GROUP BY id
HAVING COUNT(CASE LENGTH(TRIM(contact)) WHEN 7 THEN 1 END) = 0;


ID
NAME


2
B


DROP TABLE table_name;
CREATE TABLE table_name (id, name, contact) AS
SELECT 1, 'A', '3265489' FROM DUAL UNION ALL
SELECT 1, 'A', '       ' FROM DUAL UNION ALL
SELECT 1, 'A', '5645564' FROM DUAL UNION ALL
SELECT 2, 'B', '       ' FROM DUAL;
4 rows affected

SELECT id,
       MAX(name) AS name
FROM   table_name
GROUP BY id
HAVING COUNT(contact) = 0;
SELECT id,
       MAX(name) AS name
FROM   table_name
GROUP BY id
HAVING COUNT(CASE LENGTH(TRIM(contact)) WHEN 7 THEN 1 END) = 0;


ID
NAME


2
B