Elegant Solutions to Complex Problems

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 i² = -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

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:

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 b₁b₂i² with ­b₁b₂ (because i² = -1). The result of complex multiplication relies on the assumption that the equation i² = -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 a² + b². Both sides of complex number division equations are multiplied by a₂ ­ b₂i, so the divisor becomes (a₂ + b₂i)*(a₂ ­ b₂i). This multiplication of a complex number and its conjugate results in a divisor of a₂² + b₂² . 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.