Time, numbers, and character formats in Oracle

This article is intended for database administrators who want to learn about Oracle. It is hoped that this article will give you a deeper understanding of the time, numbers, and character formats in Oracle. BKJIA database also recommends the Oracle database development and application guide to help you better understand this article.

Character format

There are two types of strings in Oracle:

1. semantic comparison of space filling

2. semantic comparison of non-filled Spaces

Semantic comparison of space filling:

If the two strings have different lengths, Oracle will first add spaces at the end of the short string until the two strings are of the same length.

Oracle then compares the values of two strings in character order. If different characters are found, the values are determined by field order. If no different characters are found, Oracle considers the two strings to be equal. This rule means that if only the tail number contains spaces with different numbers, the two strings are considered to be equal. For example, 'A' = 'A '.

Using the space filling semantic comparison, Oracle only targets char, nchar, literal, OR values returned by the user function.

Semantic comparison of non-fill spaces:

Oracle compares the values of two strings in character order. If the character value is large, it is large. If the two strings have different lengths, the character values are large; otherwise, the length is large, such as 'B'> 'AB', 'AB'> 'A '. if the length is equal and the characters in each position are the same, it is considered equal.

Compared with non-space-filling semantics, Oracle targets varchar2 and nvarchar2.

1. Pay attention to the use of char. char is a fixed-length type. If the length is insufficient, spaces are filled at the end. Comparison of the comparison sizes uses spaces to fill the semantic comparison.

2. Note the connection between char and other characters, as shown in figure

 
 

  
  
Declare
  
  
A char (3): = 'AB ';
  
  
B char (6): = 'AB ';
  
  
Begin
  
  
Dbms_output.put_line (a | 'C'); -- there is a space between AB and c in AB c.
  
  
Dbms_output.put_line (B | 'C'); -- AB c contains four spaces between AB and c.
  
  
If a | 'C'> B | 'C' then
  
  
Dbms_output.put_line (1); // set up, use non-semantic comparison.
  
  
End if;
  
  
End;
 
 

3. in the SQL statement that uses char or the table type definition, it is determined to be a fixed length. Otherwise, problems may occur. If there is a char, such as a query, for example, if where charstring = 'abcd', it is best to have the same literal size as the char length.

4. In fact, the efficiency of char is no different from that of varchar2. Tom tested.