SELECT CASE 
         WHEN ( Length (:fullname) - Length (Replace (:fullname, '||', ':')) ) > 
              2 THEN 
         'morethen 3 is not possible' 
         WHEN ( Length (:fullname) - Length (Replace (:fullname, '||', ':')) ) < 
              2 THEN 
         'Full name' 
         ELSE 'Nothing' 
       END "name" 
FROM   dual 

WITH data(fullname) AS
 (SELECT 'AAAAAA'  FROM dual UNION ALL
  SELECT 'A||'     FROM dual UNION ALL
  SELECT 'A||||'   FROM dual UNION ALL
  SELECT 'A||||||' FROM dual UNION ALL
  SELECT ''        FROM dual UNION ALL
  SELECT NULL      FROM dual)
SELECT fullname, Replace (fullname, '||', ':')rep, Length (fullname) - Length (Replace (fullname, '||', ':'))len, CASE 
         WHEN ( Length (fullname) - Length (Replace (fullname, '||', ':')) ) > 
              2 THEN 
         'morethen 3 is not possible' 
         WHEN ( Length (fullname) - Length (Replace (fullname, '||', ':')) ) < 
              2 THEN 
         'Full name' 
         ELSE 'Nothing' 
       END name 
  FROM data;

FULLNAME   REP LEN      NAME
---------------------------------------------
AAAAAA	AAAAAA	0	Full name
A||	A:	1	Full name
A||||	A::	2	Nothing
A||||||	A:::	3	morethen 3 is not possible
			Nothing
			Nothing