I have table like this:

I only want users who have no contact or the contact length is not five.

If the user has two or more contacts and the length of one of them is five and the rest is not, then such users should not be included in the table.

so,If the customer has at least one contact length of five, I do not want that.

so, i want table like this:

Can you halp me?

CodePudding user response：

You could actually do a range check here:

SELECT id, name, contact
FROM yourTable t1
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable t2
    WHERE t2.id = t1.id AND TO_NUMBER(t2.contact) BETWEEN 10000 AND 99999
);

Note that if contact already be a numeric column, then just remove the calls to TO_NUMBER above and compare directly.

CodePudding user response：

You can use string functions length and coalesce to achieve desired results.

select id, name, contact from tableA where length(coalesce(contact, '0')) < 5

CodePudding user response：

Yet another option:

SQL> with test (id, name, contact) as
  2    (select 1, 'a', 65879 from dual union all
  3     select 1, 'a', null  from dual union all
  4     select 1, 'a', 45564 from dual union all
  5     select 2, 'b', null  from dual union all
  6     select 3, 'c', 12345 from dual union all
  7     select 3, 'c', 1234  from dual union all
  8     select 4, 'd', 32    from dual union all
  9     select 4, 'd', 324   from dual
 10    )
 11  select *
 12  from test a
 13  where exists (select null
 14                from test b
 15                where b.id = a.id
 16                group by b.id
 17                having nvl(max(length(b.contact)), 0) < 5
 18               );

        ID N    CONTACT
---------- - ----------
         2 b
         4 d         32
         4 d        324

SQL>

CodePudding user response：

COUNT analytic function can also be used to get the job done.

select id, name, contact
from (
select id, name, contact
  , count( decode( length(contact), 5, 1, null ) ) over( partition by id, name ) cnt
from YourTable
)
where cnt = 0

demo