Table under the employees have a phoneNumber field, type of varchar (12), request to add constraints make it only for the digital characters, and because the phone number have a landline number and phone number, so the length is not fixed
Such as:
'15012341234', '4161123' can add right
Add '4161123 a', 'a4161123', 'aaaaaaa', '4161 a123, 15012341234 will be displayed in violation of the constraint condition
Someone's solution is to check on baidu (phoneNumber not like '% [^ 0-9] %) but add a string containing the letter can also add success, what should be how to implement the
In the up sqlplus

CodePudding user response:

 

SQL> 
SQL> Create table test (ph varchar (20)); 
The Table created 
SQL> The alter table test add constraint ck_test check (not regexp_like (ph, '[^ 0-9])); 
Table altered 
SQL> Insert into test values (' 111111 '); 
1 row inserted 
SQL> Insert into test values (' A1111 '); 
Insert into test values (' A1111) 
ORA - 02290: a violation of check constraint conditions (ORACLE. CK_TEST) 
SQL> Insert into test values (' ABC '); 
Insert into test values (' ABC ') 
ORA - 02290: a violation of check constraint conditions (ORACLE. CK_TEST) 
SQL> Insert into test values (' 111 '); 
Insert into test values (' 111 ') 
ORA - 02290: a violation of check constraint conditions (ORACLE. CK_TEST) 
SQL> Insert into test values (' 111 aa111 '); 
Insert into test values (' 111 aa111) 
ORA - 02290: a violation of check constraint conditions (ORACLE. CK_TEST) 
SQL> Insert into test values (' Chinese people '); 
Insert into test values (' Chinese people ') 
ORA - 02290: a violation of check constraint conditions (ORACLE. CK_TEST) 
SQL> Select * from the test; 
PH 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
111111 
SQL> Drop table test purge; 
Table dropped 

SQL> 

CodePudding user response:

reference 1st floor wmxcn2000 response:

Why not not regexp_like (ph, '[^ 0-9] +') of one or more?
[^ 0-9] is not the first is a number?

CodePudding user response:

refer to the second floor baidu_36457652 response:

Quote: refer to 1st floor wmxcn2000 response:

Why not not regexp_like (ph, '[^ 0-9] +') of one or more?
[^ 0-9] is not the first is a number?

^ the first
In [^] are operating in brackets

Numbers do not add the +, Canada is no bai

CodePudding user response:

Could you tell me how to implement in mysql?

CodePudding user response:

To learn,,, is to learn SQL

CodePudding user response:

Regular expressions,,,, to learn