Use T-SQL and UDFs to manipulate complex numbers
User-defined functions (UDFs), which Microsoft introduced with SQL Server 2000, give you enormous power to provide elegant programmatic solutions to data-handling problems. Without UDFs, certain problems are too complicated to solve within SQL Server and require a solution in the client application. Such a solution incurs round-trip communications between the client and the server even if you need only the results of computations on the base data. One such area of complicated problems is the manipulation of complex (or imaginary) numbers, which themselves provide elegant mathematical solutions to certain problems that are too complex to solve with real numbers. But by using UDFs to encapsulate the complex numbers' algebra, you don't need to worry about the mathematical complexity.
The standard form for representing any complex number is z = a + bi. The complex number z is made up of two parts—one real and one imaginary—where a and b are real numbers and i is the square root of -1. We refer to a as the real part of z, and b as the imaginary part of z. Complex numbers are also called imaginary numbers because traditional algebra doesn't supply a solution for the equation i2 = -1. However, to solve difficult problems, you can imagine that a solution to the equation really exists—hence i = Ö1. In fact, imaginary numbers are widely used in many fields (e.g., physics, medicine, electronics, graphics).
You can depict complex numbers geometrically in a two-dimensional plane where the x-axis is the real axis and the y-axis is the imaginary axis. Such a plane is called the complex plane, or simply the z-plane. You can represent a complex number in the complex plane as either a point with the coordinates (a, b) or as a vector that starts at the origin (0, 0) and ends in the point with the coordinates (a, b), as Figure 1 shows.
Why Use T-SQL?
As you can imagine, a complex number requires special handling. Also, the algebra of complex numbers includes unique calculations and arithmetic operations. T-SQL currently doesn't support object-oriented capabilities, which means that you can't create classes to represent complex numbers. In T-SQL, you can either store the real and imaginary parts as two separate values or store the whole complex number in a character string—for example, 1 + 2i. Supporting complex numbers in an object-oriented environment such as C++ is much easier. In such an object-oriented environment, you can create a class to represent complex numbers. You can even overload arithmetic operators such as addition (+), subtraction (-), multiplication (*), and division (/), which can make complex-number manipulation even more natural.
The disadvantage of handling complex numbers in the client application is that you need to bring all the base data to the client first. Using T-SQL to provide a solution lets you exploit T-SQL's power to manipulate sets of data—it returns only the results that the client application needs. Although T-SQL doesn't include object-oriented capabilities, it lets you write UDFs that you can embed in your queries, a capability that makes UDFs the ideal T-SQL tool for manipulating complex numbers.
Storing and Splitting Complex Numbers
To maintain complex numbers' appearance, you can store them in a variable-length character (varchar) string. You can even create a user-defined data type (UDDT) called Complex to use in your column and variable definitions:
EXEC sp_addtype complex, 'varchar(50)'
If you take this approach, you need to make sure that any value that is supposed to represent a complex number is legal. You might also need to extract the real and imaginary parts of the number separately so that you can perform calculations that require only one part of the complex number.
Now let's start coding. Your first task is to write a function that accepts a varchar string representing a complex number as an argument and that checks whether the string is a legal complex number. This function should return 1 if the value is a legal complex number, and 0 if it isn't. The code in Listing 1 creates a function called cxValid that performs these tasks.
The cxValid function performs a series of tests on the input value @cx and aborts if any of the tests fail. First, the function trims the input value from leading and trailing spaces. Note that because the argument you supply to the function is treated as an input parameter, any changes you make to the argument inside the function don't affect the argument's value outside the function's scope. An input parameter is actually a copy of the value you supply to the function, not a pointer to the value's memory address.
The first test checks whether the rightmost character is the letter i. If the string passes this test, the code removes the rightmost character. Next, the code uses the pattern search PATINDEX('%_[-+]%', @cx) + 1 to store the position of the middle sign (-/+) in the variable @signpos. This pattern search returns the position of the letter that appears before the + or - sign that should exist in the middle of the complex number. Then, the pattern search adds one to the letter's position to specify the sign's exact position. Note the importance of the underscore, which represents a single character. If the real part of the complex number has a preceding sign (e.g., -3), that sign doesn't qualify under this pattern search because the pattern search requires at least one character before the sign. If no such sign exists in the middle of the supposed complex number, PATINDEX returns 0.
After you store the middle sign's position in a variable, it's simple to extract the real and imaginary parts of the complex number and use the IsNumeric function to check whether they are numeric. If the string passes all the tests, the cxValid function returns 1 to specify that the string is valid; otherwise, it returns 0.
If you want to allow only valid complex numbers in columns you've defined with the Complex UDDT, you can create a rule that implements the same logic as the dbo.cxValid function, then bind the rule to the Complex UDDT. Listing 2 shows how to create such a rule and bind it to the Complex type. Note that you can't use the cxValid function inside the rule because SQL Server allows only system functions inside rules. Also, you must write each rule as one expression, so you can't break the validating logic into several steps as you can in the cxValid function.
Because you're using variable-length character strings to store complex numbers, you have a lot of flexibility in how you represent them. For example, here are a few legal variations of the same complex number:
1+2i
1 + 2i
+1 + 2i
1 + 2i
1.000 + 2.0i
Prev. page
[1]
2
next page 
