-1

My table having invalid mobile and I want to mention in my select statement mobile number column values as invalid/valid.

create table customer (name varchar2(20),mobile varchar2(20));
insert into customer  (name, mobile) values ('A','1234');
insert into customer  (name, mobile) values ('B','F2345');
insert into customer  (name, mobile) values ('C','AYGPB');
insert into customer  (name, mobile) values ('D','9876541230');
insert into customer  (name, mobile) values ('E','6541237890');
insert into customer  (name, mobile) values ('F','9638520741');
insert into customer  (name, mobile) values ('G','9QWERTYUIOP');
insert into customer  (name, mobile) values ('H','');
insert into customer  (name, mobile) values ('I','12TGHY');
insert into customer  (name, mobile) values ('J','SACHIN');
insert into customer  (name, mobile) values ('K','9999999999');
insert into customer  (name, mobile) values ('L','6666666666');

commit;

I need output like below

enter image description here

Can you please provide me the select statement?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Bala S
  • 495
  • 1
  • 6
  • 17
  • 3
    Please explain the rules for determining if a number is valid or not. – Gordon Linoff Jun 24 '21 at 11:45
  • insert into customer (name, mobile) values ('L','1234567980'); insert into customer (name, mobile) values ('L','0234567980');insert into customer (name, mobile) values ('L','2234567980');insert into customer (name, mobile) values ('L','3234567980');insert into customer (name, mobile) values ('L','4234567980');insert into customer (name, mobile) values ('L','5234567980'); This is also Invalid mobile number. I mean starting with 0,1,2,3,4,5 all the number are Invalid, Can you re-write the query – Bala S Jun 24 '21 at 12:15
  • 1
    Does this answer your question? [How to validate phone numbers using regex](https://stackoverflow.com/questions/123559/how-to-validate-phone-numbers-using-regex) or [Phone validation regex](https://stackoverflow.com/questions/8634139/phone-validation-regex) – astentx Jun 24 '21 at 12:48

2 Answers2

4

Your rules are unclear, but the idea is a case expression with regexp_like():

select t.*,
       (case when mobile is null then 'No data'
             when mobile in ('9999999999', '6666666666') or
                  not regexp_like(mobile, '^[0-9]{10}')
             then 'Invalid data'
             else mobile
        end) as valid_invalid
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Once you figure out what makes a mobile phone number valid, adjust the following code:

SQL> select name,
  2         mobile,
  3         --
  4         case when mobile is null then 'No data'
  5              when not regexp_like(mobile, '^\d+$') then 'Invalid data'
  6              else mobile
  7         end valid_invalid
  8  from customer;

NAME                 MOBILE               VALID_INVALID
-------------------- -------------------- --------------------
A                    1234                 1234
B                    F2345                Invalid data
C                    AYGPB                Invalid data
D                    9876541230           9876541230
E                    6541237890           6541237890
F                    9638520741           9638520741
G                    9QWERTYUIOP          Invalid data
H                                         No data
I                    12TGHY               Invalid data
J                    SACHIN               Invalid data
K                    9999999999           9999999999
L                    6666666666           6666666666

12 rows selected.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • It allows arbitrary digits in the number, but it should have no more than 15 by E.164 standard. – astentx Jun 24 '21 at 12:48