Suitable constraints for varchar2 (5) Data Types in SQL

In the database class, I started with SQL and talked about create table and constraints. I gave them a question on the tiger bar, it is a puzzle found from the famous SQL Puzzles and Answers. The Answers in the original book are actually incorrect.

Create a new table that contains three columns, all of which are varchar2 (5) data types. Add appropriate constraints to this table, in this way, the first column can only contain "non-letter strings", which are all composed of digits or special characters), and the second column can only contain "character strings containing letters ", the third column can only enter "strings all letters"

Write related create table statements

As a result, a student quickly answered the question and corrected the error in the original book.

Then the answer to the question is given:

This is his original answer.

create table x (a varchar2(5) check (upper(a)=a and lower(a)=a), b varchar2(5) check (not upper(b)=b or not lower(b)=b), c varchar2(5) check (length(translate(c,' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '))=0));

There are still some flaws in this answer.

1. The not null constraint is also required for each column. Later, he quickly discovered this problem and pointed out the correct one.

2. The first check can be simplified to upper (a) = lower (a), and the second check can be simplified to upper (B) <> lower (B)

3. The translate function used by the third check is a magic pen. This is wrong in the original book. However, the versatility of translate is not good, so it is best to have a better method of universality.

There are still many questions about Tiger's answer. According to the answer to the third question, the letters here refer to English letters. Otherwise, the answer to the third question is incomplete.

The following describes the letters mentioned in the question according to English letters. Of course, letters represent English letters.

In fact, the answer to the first and second questions is the same. The UPPER and LOWER functions are used to determine whether a character is an English character. This method is not rigorous:

SQL> SELECT UPPER(CHR(42660)), LOWER(CHR(42660)) FROM DUAL; UP LO -- -- Δ δ

This is the simplest example. To know how many results do not meet this condition, run the following PL/SQL code:

SQL> SET SERVEROUT ON SIZE 1000000 SQL> BEGINITPUB 2 for I IN 1 .. 65535 LOOP 3 if upper (CHR (I ))! = LOWER (CHR (I) and length (CHR (I) = 1 4 DBMS_OUTPUT.PUT_LINE (I | ':' | CHR (I); ITPUB personal space t F [Z + D; Y 5 end if; 6 end loop; 7 END; 8/

Of course, most of the results here are an English letter with a non-printable character, but since this phenomenon may exist, it can be considered as a standard answer. What's more, there are also letters from various countries, including the Greek characters given in the above example.

There is a lot of uncertainty in the method of judging whether UPPER and LOWER are English characters based on experience or subjective judgment, unless the output results of all characters UPPER and LOWER are verified as above.

In fact, you can use functions such as LTRIM, REPLACE, and TRANSLATE to determine this problem. Of course, you can also use regular expressions for higher versions. All of the above methods share the same idea in the function. For example, the LTRIM method is ltrim (UPPER (C), 'abcdefghijklmnopqrstuvwxyz ') is null.

1. Tips for creating a local temporary table in SQL Server
2. Concurrent queries allow SQL Server to accelerate operation
3. SQL Server 2005 FAQs