Elegant Solutions to Complex Problems

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?

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

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:

1. Uses the REPLACE function to remove all spaces
2. Splits apart the real and imaginary parts (the middle sign remains attached to the imaginary part) and removes all redundant trailing zeros
3. Concatenates the real and imaginary parts
4. Adds single spaces before and after the middle sign
5. 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

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