I am going to carry one with 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 exploring the Bitwise Operators today. Every piece of data on a computer is stored as a series of bits, either 1 or 0. Four bits are grouped together to become what is called a nibble. Two nibbles are combined to become a byte. The least significant bits are named lower nibble and the…
Assignment Operator
I am going to continue the 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 exploring the Assignment Operator today. This equality symbol = in mathematics. There are two ways in which the assignment operator can be used: alias – associating a column heading with a expression or storage – placing the results of a expression into a variable. The TSQL example below compares the assignment operator against old and…
Arithmetic Operators
I am going to start a 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 exploring the Arithmetic Operators today. The very first practical use of modern day computers was to solve mathematical problems. The ENIAC computer in 1946 was used by the United States Army to create artillery firing tables. TSQL supports the following five basic operations: addition, subtraction, multiplication, division and modulus. Most people know the first four…
String Functions – UPPER()
I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the UPPER() function today. The UPPER() function takes a source string as input and returns a output string with all alpha characters converted to upper case. The following example converts the favorite movie title of mine to upper case.
|
1 2 |
-- Make my string upper case select upper('wizard of oz') as str_phrase |
|
1 2 3 4 5 |
output: str_phrase ------------ WIZARD OF OZ |
One thing to always worry about when using TSQL functions is how will it react with empty strings or NULL values? Both empty strings and NULL values as…
String Functions – UNICODE()
I am going to write a series of very short articles or tidbits on Transaction SQL (TSQL) functions. I will exploring the UNICODE() function today. Transaction SQL (TSQL) supports the UNICODE() function that takes a NCHAR or NVARCHAR datatype as input and returns a INT datatype as output. The output value corresponds to the unicode characters location in a chart. This value can range from 0 to 65535. The following example returns the unicode value for a space which is a value of 32.
|
1 2 3 4 5 |
-- A simple space declare @var_tmp1 nchar = ' '; -- Return ascii # select unicode(@var_tmp1) as unicode_val; |
|
1 2 3 4 5 |
output: unicode_val ----------- 32 |
One thing to always…
String Functions – SUBSTRING()
I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the SUBSTRING() function today. The SUBSTRING() function allows a developer to slice and dice a string into pieces. It takes a source string [T], a integer starting position [S], and a the number of characters [C] to return as input parameters. It returns a string of [C] characters from position [S] as output. The example below extracts one word from a famous movie title.
|
1 2 |
-- Extract one word select substring('wizard of oz', 8, 2) as str_word |
|
1 2 3 4 5 |
output: str_word -------- of |
One thing to…
String Functions – STUFF()
I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the STUFF() function today. The STUFF() function takes a source string [S], a starting position [P], a total length [L] and a replacement string [R] as input parameters. It removes [L] characters from the source string [S] starting at position [P] and replaces those changes with string [R]. From the definition of this function, one could easily write a custom user defined function with the same functionality using the…
String Functions – STR()
I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the STR() function today. The STR() function takes a floating point number [F] to be converted to a string, a integer number [L] representing the total length, and a integer number [D] representing the number of decimal digits as input. It returns a formatted string representing the floating point number. The length [L] includes decimal point, sign, numbers and spaces. The decimal digits [D] represents the number of digits…
String Functions – SPACE()
I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the SPACE() function today. The SPACE() function takes a positive integer replication count [C] as input. The function returns a string composed of a single space concatenated [C] times to itself as output. The example below adds one, two, and three spaces inside the title of a favorite movie of mine.
|
1 2 |
-- Add 1 + 2 + 3 spaces select space(1) + 'wizard' + space(2) + 'of' + space(3) + 'oz' as str_phrase |
|
1 2 3 4 5 |
output: str_phrase ---------------- wizard of oz |
One thing to always worry about when using TSQL functions is how will it react with…
String Functions – SOUNDEX()
I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the SOUNDEX() function today. The SOUNDEX() function calculates a four-character code that is based on how the string sounds when spoken. Please see my prior blog entry for a sample use of this function in conjunction with the DIFFERENCE() function. Soundex was developed by Robert C. Russell and Margaret K. Odell and patented in 1918 / 1922. The United States government created a variation named the American Soundex which…