PATINDEX(<pattern>, <source string>) and CHARINDEX(<pattern>, <source string>)

PATINDEX returns the first occurrence of a string inside a larger string. You can use this function to search a BLOB text field. Wildcards are available in the pattern field. CHARINDEX works the same way, but wildcard characters are not available with this function, and it cannot be used to search text types.

The titles table contains a text field, “notes.” This query searches for books that have a note containing the word “Computer” in it, and the character position where the word appears

select   title_id, patindex(‘%Computer%’, notes) “Start At”
from     titles
where    patindex(‘%Computer%’, notes) <> 0

----------------------------*/
title_id  Start At
--------  ----------
BU7832       28
PC8888       45
PC9999       17
PS1372       95
PS7777       98

The PATINDEX() function is used in two places. The one in the WHERE clause searches through all the text data looking for the word “Computer.” (I could have made it case insensitive by using the pattern “%[Cc]omputer%”.) If a match is found, the title ID is reported along with the position of the word “Computer.”

Functions That Are Neat, But I Don’t Use Much

A brief word on a few functions for which I haven’t found much use:

SPACE(<int>)—Returns a string of (int) spaces.

REVERSE(<source string>)—Reverses the source string. For example, it turns “Ben” into “neB.” If you find a valid business use for this function, e-mail me. I would love to hear about it.

REPLICATE(<pattern>, <int>)—Returns a string with the pattern repeated (int) times.

SOUNDEX(<source string>)—Returns a four-digit soundex code to represent the phonemes in the source string. The ultimate “neat but never used” function, the scenario for its use is a telephone operator, taking a name over the phone, who wants to search for a name that sounds like “Smith.” He would match a SOUNDEX() on Smythe, Smyth, Sumith. SOUNDEX() knocks out vowels in a word and uses the first letter to form its codes.

DIFFERENCE(<char1>, <char2>)—Evaluates the soundex difference between char1 and char2 on a scale of 0 to 4, where 4 is an exact match and 0 is a complete mismatch.

Math Functions

There are a few math functions I have found quite useful for solving some otherwise difficult problems. Most of these will be used in a clever fashion in later chapters. For now, here are some simple examples to demonstrate how some math functions can be used.

A Brief Math Example

You have a table with two columns in it: CustID and InvAmt. The InvAmt column (Invoice Amount) contains negative dollars for amounts you owe (credits) and positive dollars for amounts you are owed (debits). This query will display a report with three columns, labeled CustID, debit, and credit (I’ve added the InvAmt column for your reference):

select  CustID,
        InvAmt * ($0.5 * (sign(InvAmt) + 1)) “Debit”,
        InvAmt * ($0.5 * (sign(InvAmt) - 1)) “Credit”,
        InvAmt
from    invoice

CustID  Debit              Credit                  InvAmt
------  -----------        -----------------       -----
1       0.00               500.00                  -500.00
1       0.00               250.00                  -250.00
2      75.00                 0.00                    75.00
3      35.00                 0.00                    35.00
3     100.00                 0.00                   100.00
2      90.00                 0.00                    90.00
1       0.00               250.00                  -250.00

(7 row(s) affected)

This query uses some math functions to arrive at a clever answer to the problem. Let me break down the two complicated columns.

Start on the inside of the parentheses and work out. First, take the sign of the data. For the Debit column, I want data that is positive. So take the sign and add one to it. Positive numbers go to 2, negative numbers to 0, and zeroes to 1.

Now, multiply that result times $0.50. If I multiply by 0.5, the server thinks I want a float result, but if I multiply by 50 cents, the data type remains a smallmoney. Positive numbers go back to one: (2 × 0.5 = 1). Negative numbers go to zero (0 × 0.5) and zeroes go to zero (1 × 0.5). Now, multiply this product by the data. Only positive numbers retain a nonzero value.

In the credit column, we start off the same way. This time, subtract one from the sign, so negative numbers go to –2, zeroes go to –1, and positive numbers go to zero. Multiply by 50 cents to get 0 for zeroes and positive numbers, and –1 for negative numbers. Multiplying this by the data value (–1 * –(some value) = positive result) yields the credit column.