Presenting complex numbers in inconsistent forms looks awkward, and consistency is easier to code against, so you might prefer a consistent representation for your complex numbers. For example, you might choose to present the leading sign of the real part only if the sign is negative, surround the middle sign with single spaces, trim all leading and trailing spaces, and trim redundant trailing zeros. You can create a function that accepts a legal complex number as an argument and returns its standardized form: [-]a {+ | -} bi. For example, the cxStandardize function in Listing 3 performs the following steps:
- Uses the REPLACE function to remove all spaces
- Splits apart the real and imaginary parts (the middle sign remains attached to the imaginary part) and removes all redundant trailing zeros
- Concatenates the real and imaginary parts
- Adds single spaces before and after the middle sign
- Removes the + sign, if one exists, before the real part
When you start performing complex arithmetic, you need to extract the real and imaginary parts of a complex number; again, you can write functions to perform this task. Listing 4, page 61, contains the creation script for the cxGetReal and cxGetImaginary functions, which are fairly simple. These functions use the PATINDEX function in the same way the previous functions did—to locate the position of the middle sign and return the relevant part, either to the left of the sign or starting from the middle sign and to the right of it (not including the character i).
In the same way that you might need to separate a string representing a complex number into its real and imaginary parts, you might also need to do the opposite—form from supplied real and imaginary parts a string representing a complex number. You use the cxStrForm function from Listing 5 to form a string from four parts: the real part, the imaginary part's sign, the imaginary part, and the character i. The real and imaginary parts are supplied to the cxStrForm function as arguments; the sign is calculated using the SIGN() function with the imaginary part provided to the function as an argument. Then all you have to do is concatenate all the parts to form the result string. The result string is provided to the cxStandardize function as an argument to generate a standardized complex number.
Complex Operations
Now that you've laid the foundation, you can move on to implementing complex arithmetic operations. Complex numbers have their own rules for addition, subtraction, multiplication, and division, all of which you can implement through UDFs.
To add two complex numbers, you simply add the real and the imaginary parts separately, as Figure 2 shows. You can represent the result of complex addition as a vector in the complex plane. If you form a parallelogram from the vectors representing the complex numbers you're adding, the result vector is the diagonal of the parallelogram starting at the origin and ending at the counter vertex (the coordinates of the result). Figure 3 shows a geometrical representation of complex addition.
Implementing the cxAdd function, which the script in Listing 6 creates, is an easy task now. The function accepts two arguments that represent complex numbers, and performs four steps. First, cxAdd uses the cxValid function to check whether both arguments are valid and aborts, returning NULL, if either argument is not a valid complex number. Then, it uses the cxGetReal and cxGetImaginary functions to extract the real and imaginary parts from both arguments and stores the parts in numeric variables. Next, the function calculates the sum of the real and imaginary parts according to complex addition rules. Finally, cxAdd uses the cxStrForm function to return a standardized string form of the resulting complex number.
You perform complex subtraction in much the same way as complex addition. You subtract the real and the imaginary parts separately, as Figure 4 shows. In geometrical terms, you can think of complex subtraction as a variation of complex addition: You simply negate the second complex number and perform complex addition, as Figure 5 shows. The script in Listing 7 creates the cxSubtract function. You implement the cxSubtract function in the same way you implement the cxAdd function. The only thing that changes is the formula that calculates the resulting real and imaginary parts.
Figure 6 shows how to multiply two complex numbers. Note that at some point in the calculation, you replace b1b2i2 with b1b2 (because i2 = -1). The result of complex multiplication relies on the assumption that the equation i2 = -1 has a solution; otherwise, you couldn't simplify the equation. Listing 8 shows the script that creates the cxMult function. Again, you implement the cxMult function in much the same way as you did the other functions; only the formulas that calculate the resulting real and imaginary parts change.
Complex division makes use of complex number conjugates. The conjugate of the complex number z is represented as z, which has the same real part as z's real part and an imaginary part that is the negative of z's imaginary part. If you multiply a complex number by its conjugate, you get a2 + b2. Both sides of complex number division equations are multiplied by a2 b2i, so the divisor becomes (a2 + b2i)*(a2 b2i). This multiplication of a complex number and its conjugate results in a divisor of a22 + b22 . Figure 7, page 63, shows how to perform complex division. You can use the code in Listing 9 to create the cxDivide function, which uses formulas to calculate the real and imaginary parts of the resulting complex number.
Polar Form of Complex Numbers
You can also represent complex numbers in polar form (r,q). You use the polar form when the angle of the vector representing the complex number also has significance in your calculations. The polar form uses the vector size (r), which is the distance between the origin and a point (a, b) in the complex plane. The angle (q) is the angle between the real axis and the vector representing the complex number. Figure 8 shows the polar form of a complex number.
The vector size of the complex number z is also referred to as z's magnitude, absolute value, or modulus. You use the Pythagorean theorem to calculate vector size, as Figure 9 shows. Figure 10 shows a triangle formed from the points (0, 0), (a, 0), (a, b). According to the Pythagorean theorem, the diagonal's length (r) is the square root of the sum of the triangle's sides raised to the power of two. Thus, you can represent a complex number as z = rcosq + irsinq. This equation supplies the basis for Euler's equation, which Figure 11 shows and which is widely used in complex algebra. Using polar notation, you can write a UDF that calculates vector size of a complex number by simply implementing the Pythagorean theorem, as Listing 10 shows.
These examples give you a general idea of how to implement complex algebra with UDFs. Building on these basics, you can implement other complex functions, such as finding the nth root of a complex number, that you might need to use in your implementations.
Implementing Complex Functions
Besides being a good exercise both in writing functions and in solving complex algebra problems (and a cool topic in its own right), manipulating complex numbers in a SQL Server database has practical implications as well. You can store large amounts of complex numbers in tables, issue queries against the numbers from your client application to populate recordsets, and navigate between the records returned in the client application. The client application doesn't need to locally store such large amounts of data. Furthermore, some applications work with a great deal of complex base data even though they really need only the aggregated results of calculations on the set of base data. Using only the aggregated results reduces round-trips between the client and the server and exploits SQL Server's powerful capabilities in manipulating sets. To illustrate the power of complex numbers, I show you how you can manipulate complex functions in your queries. Then, in the sidebar "Sound and Image Processing," I present an example of how you can implement them in sound and image processing. Fernando Guerrero demonstrates another practical example of using complex numbers in a SQL Server database in the sidebar "AC Circuits and T-SQL," which you can find by entering InstantDoc ID 15665 at http://
www.sqlmag.com.
Using Complex Functions
To try complex functions, you can create a simple table with two columns in which to store complex numbers, then issue queries against that table. For example, Listing 11 creates the table ComplexNumbers and populates it with sample complex numbers. To perform addition, subtraction, multiplication, and division on each pair, you simply embed the complex functions, as the query in Listing 12 shows. Table 1 shows the results of this query. Each row in the result set contains two complex numbers and the result of their addition, subtraction, multiplication, and division.
Performing aggregated complex arithmetic operations is more difficult than performing single calculations. Suppose you want to calculate the sum of the products of all the pairs of complex numbers in the ComplexNumbers table. Although UDFs are powerful and provide a lot of functionality, T-SQL doesn't let you develop aggregate UDFs that operate on sets of rows and that you can embed in a query like any other system-supplied aggregate functions—for example, SUM(). You can, however, use a trick: T-SQL lets you issue an assignment query that doesn't return the query results to the client application but instead assigns a value to a variable. You can declare a variable that represents a complex number and initialize it with 0 + 0i. Then you can use this variable in a query that iterates all rows in the table and, for each row, adds the result of the product of each row's pair of complex numbers. Listing 13 contains an example of this type of query.
UDFs in SQL Server 2000 give you enormous power to deal with problems that were almost impossible to solve in earlier releases of SQL Server. Besides manipulating complex numbers in a database, you can use UDFs in many other ways to provide a programmatic solution to problems.