I am going pick up writing my series of very short articles or tidbits on Transaction SQL Operators. An operator is a symbol specifying an action that is performed on one or more expressions.
I will continue examining the Comparison Operators today.
These comparison operators are known as the less than <, less than or equal to <=, equal to =, greater than >, and greater than or equal to >= symbols in inequality and equality mathematics.
I will be examining how a sample operator works with character, date/time, money and binary data types. Remember, character data types use the ASCII or UNICODE chart to determine if one character is greater than another. All other data types are based upon the value stored on disk or in memory.
The first example below shows the less than operator used with character data type.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- -- Comparision operators - Character data type -- -- Declare variables declare @chr_one varchar(2) = 'AB'; declare @chr_two varchar(2) = 'ab'; -- Both variables are the same print @chr_one; print @chr_two; print ' '; -- Ansi operator - < if @chr_one < @chr_two print 'CHAR - Upper is less than lower.'; else print 'CHAR - Lower is less than upper.'; print ' '; |
The output of the first example is listed below.
|
1 2 3 4 5 6 |
output: AB ab CHAR - Lower is less than upper. |
The second example below shows the equal operator used with date/time data type. In this example, both variables are equal due to precision loss during rounding.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- -- Comparision operators - Date/Time data type -- -- Declare variables declare @dt_one smalldatetime = '2013-05-01 23:59:59.999'; declare @dt_two smalldatetime = '2013-05-02 00:00:00.000' -- Both variables are the same (rounding) print @dt_one; print @dt_two; print ' '; -- Ansi operator - = if @dt_two = @dt_one print 'DATE / TIME - Both variables are equal.'; else print 'DATE / TIME - Both variables are not equal.'; print ' '; |
The output of the second example is listed below.
|
1 2 3 4 5 6 |
output: May 2 2013 12:00AM May 2 2013 12:00AM DATE / TIME - Both variables are equal. |
The third example below shows the greater than operator used with money data type.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- -- Comparision operators - Money data type -- -- Declare variables declare @num_one smallmoney = 321.22; declare @num_two smallmoney = 312.21; -- Both variables are not the same print @num_one; print @num_two; print ' '; -- Ansi operator - > if @num_one > @num_two print 'MONEY - Variable one is greater than two.'; else print 'MONEY - Variable one is not greater than two.'; print ' '; |
The output of the third example is listed below.
|
1 2 3 4 5 |
output: 321.22 312.21 MONEY - Variable one is greater than two. |
The fourth example below shows the not equal to operator used with binary data type.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- -- Comparision operators - Binary data type -- -- Declare variables declare @bin_one binary(1) = 0x3E; declare @bin_two binary(1) = 0xE3; -- Both variables are not the same print @bin_one; print @bin_two; print ' '; -- Ansi operator - <> if @bin_one <> @bin_two print 'BINARY - Variable one is not equal to two.'; else print 'BINARY - Variable one is equal to two.'; print ' '; |
The output of the fourth example is listed below.
|
1 2 3 4 5 |
output: 0x3E 0xE3 BINARY - Variable one is not equal to two. |
Next time, I will be chatting about how to use Compound Operators when writing Transaction SQL scripts.
