3. Give up char, before making a big mistake!

/*************************************** **********
Iii. Topic: discard char, before making a big mistake!

Char, fixed length. If the length is insufficient, spaces will be filled at the end.
Varchar, with an indefinite length. The length does not necessarily reach the maximum value.
**************************************** *********/

========================================================== ============================================
3.1.length: returns the function of character length.
The length functionsreturn the length of char. Length calculates length usingcharacters as defined by the input character set.
-- Returns the length in characters.
Lengthb usesbytes instead of characters.
-- Returns the length in bytes.
Lengthc usesunicode complete characters.
-- Returns the length in units of Unicode full characters.
Leng2usesucs2 code points.
-- Returns the length in the unit of ucs2 code points.
Length4 usesucs4 code points.
-- Returns the length in the unit of ucs4 code points.
========================================================== ============================================
Create Table test4
(
C char (4 ),
VC varchar (4 ),
NC varchar (4)
);

Insert into test4 values ('1', '12', '10 ');
Insert into test4 values ('3', '3', '2013 ');
Insert into test4 values ('', '20160301', '20160301 ');
Insert into test4 values ('25', '', '9 ');
Insert into test4 values ('A', 'hes', '50 ');
========================================================== ============================================
3.2.char and varchar Length
Select C, length (C), Vc, length (VC) from test4; -- Query Result: c = '1' is displayed, and spaces are displayed.

Select C, length (C), lengthb (C), lengthc (C), dump (C), -- dump: Where c = '1, typ = 96 Len = 4: 49,32, 32, 32. The last three 32 characters indicate spaces.
VC, length (VC), lengthb (VC), lengthc (VC), dump (VC) -- dump: the obvious difference is that the actual length is not the maximum length.
From test4;

3. Compare the size of a string (conventional sequence type)

Select C, dump (c)
From test4 order by C; -- sorting result: '', '1', '25', '3', 'A'

Select VC, dump (VC)
From test4 order by VC; -- sorting result: '', '2013', '12', '3', 'hes'

. Comparison of numerical values in sorting. Important application: When all data in the dense storage is numbers.
Select NC, dump (NC)
From test4
Order by NC; -- sorting result: 10, 1300, 210,50, 9
-- If you want to sort all numeric values, order by C + 0 or order by to_number (NC ).
Select NC, dump (NC)
From test4
Order by nC + 0; -- sorting result: 9, 10, 50,210,130 0

Select NC, dump (NC)
From test4
Order by to_number (NC); -- sorting result: 9, 10, 50,210,130 0

3. 5. String comparison
Comparison rules for filling spaces: If the length is different, spaces will be filled after the short, and will be compared after the same length;
Compare from left to right until the comparison is inconsistent or the comparison is complete.
(1) Simple Literal Value
Select * From test4 where c = '1'; -- the result is correct.

(2) The varchar value does not fill in spaces, so the char '1' of test4 is different from the varchar2 '1' of test1.
Select * From test4 where C in (Select name from test1); -- Row 0 is selected without query results. In fact, test1 contains '1 '.
-- Cause
Select dump (C), dump (name)
From test4, test1
Where test4.c = '1'
And test1.name = '1'
Result: typ = 96 Len = 4: 49,32, 32,32; typ = 1 Len = 1: 49.

(3) trim and rpad functions are often used to obtain correct results, but are not recommended.
Select * From test4 where trim (C) in (Select name from test1); -- test4 clear spaces and query results have values
Select * From test4 where C in (select rpad (name, 4) from test1); -- test1 fills in spaces and the query result has a value

-- If char exists, some character functions may be added for each comparison.
-- Char is a waste of space and may not be used in queries.

3. 6. Conclusion: Give Up char before making a big mistake!