loading..
Русский    English
04:29

CHAR and VARCHAR page 2

Let us now see what happened with the definition of data. Below is a test script.

  1. CREATE TABLE Test_char( chr CHAR, vchr VARCHAR );

  1. DELETE FROM Test_char;

  1. INSERT INTO Test_char
  2. VALUES ('1','11111111112222222222333333333344444444445555555555');
  3.  
  4. INSERT INTO Test_char
  5. VALUES ('11111111112222222222333333333344444444445555555555', '1');
  6.  
  7. INSERT INTO Test_char
  8. VALUES ('2',CAST('111111111122222222223333333333' AS VARCHAR));
  9.  
  10. INSERT INTO Test_char
  11. VALUES (CAST('111111111122222222223333333333' AS CHAR), '2');
  12.  
  13. INSERT INTO Test_char
  14. VALUES ('3', '3');

  1. SELECT * FROM Test_char;

SQL Server 2008

chr vchr
3 3

So, only one row be inserted containing one character for each column. When you insert the remaining rows we get an error message:

String or binary data would be truncated. The statement has been terminated.

which means that we should reduce the size of the row.

Although there is standard compliance, it seems to me that there is a contradiction that an explicit cast to the column type of the table does not work:

  1. INSERT INTO Test_char
  2.  VALUES (CAST('111111111122222222223333333333' AS CHAR), '2')
  3.  

PostgreSQL 8.3

chr vchr
1 11111111112222222222333333333344444444445555555555
2 111111111122222222223333333333
1 2
3 3

It may be noted the sequence in behavior: VARCHAR is arbitrary size; the second row was not inserted due to an error exceeding the size (ERROR: value too long for type character(1)); explicit value conversion to the column type of the table works, cutting off the extra characters from the right.

MySQL 5.0

VARCHAR type is not supported without specifying the size of the string. CHAR corresponds CHAR (1) – as standard. Since the explicit conversion to CHAR leaves the length of the string without changing, then into the table, defined as

  1.  CREATE TABLE Test_char( chr CHAR, vchr VARCHAR(1) );

in the result, as in SQL Server, single row will be added:

chr vchr
3 3

Conclusions. In my humble opinion, none of these databases does not meet the standard behavior in those cases where the size of type is not specified. In my opinion, PostgreSQL is most consistent in the “particular implementation”. In order to code portability, I would recommend that you always explicitly specify the size.

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
обменять с capitalist
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.