Alphanumeric Data Types - SQL

We already explained that when creating a table we must choose the appropriate structure, that is, define the fields and their most precise types, as appropriate.

To store alphanumeric values ​​(text) we use character strings.

Strings are enclosed in single quotes.

We can store letters, symbols and digits with which mathematical operations are not performed, for example, identification codes, document numbers, telephone numbers. We have the following types:

#1.  char (x): defines a fixed-length character string determined by the "x" argument. If the argument is omitted, it defaults to 1. "char" stands for character, which means character in English. Its range is from 1 to 2000 characters.

That it is a fixed length string means that, if we define a field as "char (10)" and store the value "hello" (4 characters), Oracle will fill the remaining 6 positions with spaces, that is, it will occupy the 10 positions; therefore, if the length is invariable, it is convenient to use the char type; otherwise, the type varchar2.

If we store "hello" in a defined field "char (10)" Oracle will store "hello".

#2. varchar2 (x): stores character strings of variable length determined by the argument "x" (required). That it is a variable length string means that, if we define a field as "varchar2 (10)" and store the value "hello" (4 characters), Oracle only occupies the 4 positions (4 bytes and not 10 as in the case of "char"); therefore, if the length is variable, it is convenient to use this data type and not "char", so we take up less disk storage space. Its range is from 1 to 4000 characters.

#3. nchar (x): is similar to "char" except that it allows storing ASCII, EBCDIC and Unicode characters; its range is from 1 to 1000 characters because 2 bytes are used for each character.

#4. nvarchar2 (x): is similar to "varchar2", except that it allows storing Unicode characters; its range is from 1 to 2000 characters because 2 bytes are used for each character.

5 and 6) varchar (x) and char2 (x): available in Oracle8.

#7. long: save characters of variable length; it can contain up to 2,000,000,000 characters (2Gb). It does not admit an argument to specify its length. In Oracle8 and later versions it is convenient to use "clob" and "nlob" to store large amounts of alphanumeric data.

#8. clob (Character Large OBject) and nclob: can store up to 128 terabytes of character data in the database

#9. blob (Binary Large OBject): can store up to 128 terabytes of binary data (images, video clips, sounds etc.)

If we try to store a character string longer than the one defined in an alphanumeric field, a message appears indicating that the value is too large and the statement is not executed.

For example, if we define a field of type varchar2 (10) and assign it the string 'Learn PHP' (11 characters), a message appears and the statement is not executed.

If we enter a numeric value (omitting the quotes), it converts it to a string and enters it as such.

For example, if in a field defined as varchar2 (5) we enter the value 12345, it takes it as if we had typed '12345', likewise, if we enter the value 23.56, it converts it to '23 .56 '. If the numeric value, when converted to a string, exceeds the defined length, an error message appears and the statement is not executed.

It is important to choose the right type of data according to the case.

To store strings that vary in length, that is, not all records will have the same length in a given field, use "varchar2" instead of "char".

For example, in fields where we store first and last names, not all first and last names are the same length.

To store strings that do not vary in length, that is, all records will have the same length in a given field, "char" is used.

For example, we define a "code" field that will consist of 5 characters, all records will have a 5-character code, no more, no less.

To store values ​​greater than 4000 characters, "long" must be used.