String-Manipulation Functions (IDS)

String-manipulation functions accept arguments of type CHAR, NCHAR, VARCHAR, NVARCHAR, or LVARCHAR. You can use a string-manipulation function anywhere you use an expression.

The following functions convert between upper and lowercase letters in a character string:

You cannot overload any of the string-manipulation functions to handle extended data types.

Using the LOWER Function

Use the LOWER function to replace every uppercase letter in a character string with a lowercase letter. The LOWER function accepts an argument of a character data type and returns a value of the same data type as the argument you specify.

Figure 217 uses the LOWER function to convert any uppercase letters in a character string to lowercase letters.

Figure 217. Query

SELECT manu_code, LOWER(manu_code)
   FROM items
   WHERE order_num = 1018

Figure 218. Query Result

manu_code   (expression)

PRC          prc
KAR          kar
PRC          prc
SMT          smt
HRO          hro

Using the UPPER Function

Use the UPPER function to replace every lowercase letter in a character string with an uppercase letter. The UPPER function accepts an argument of a character data type and returns a value of the same data type as the argument you specify.

Figure 219 uses the UPPER function to convert any lowercase letters in a character string to uppercase letters.

Figure 219. Query

SELECT call_code, UPPER(code_descr) FROM call_type

Figure 220. Query Result

call_code    (expression)

B            BILLING ERROR
D            DAMAGED GOODS
I            INCORRECT MERCHANDISE SENT
L            LATE SHIPMENT
O            OTHER

Using the INITCAP Function

Use the INITCAP function to replace the first letter of every word in a character string with an uppercase letter. The INITCAP function assumes a new word whenever the function encounters a letter that is preceded by any character other than a letter. The INITCAP function accepts an argument of a character data type and returns a value of the same data type as the argument you specify.

Figure 221 uses the INITCAP function to convert the first letter of every word in a character string to an uppercase letter.

Figure 221. Query

SELECT INITCAP(description) FROM stock
   WHERE manu_code = "ANZ"

Figure 222. Query Result

(expression)

Tennis Racquet
Tennis Ball
Volleyball
Volleyball Net
Helmet
Golf Shoes
3 Golf Balls
Running Shoes
Watch
Kick Board
Swim Cap

Using the REPLACE Function

Use the REPLACE function to replace a certain set of characters in a character string with other characters.

In Figure 223, the REPLACE function replaces the unit column value each with item for every row that the query returns. The first argument of the REPLACE function is the expression to be evaluated. The second argument specifies the characters that you want to replace. The third argument specifies a new character string to replace the characters removed.

Figure 223. Query

SELECT stock_num, REPLACE(unit,"each", "item") cost_per,
unit_price
   FROM stock
   WHERE manu_code = "HRO"

Figure 224. Query Result

stock_num      cost_per   unit_price

1              case          $250.00
2              case          $126.00
4              case          $480.00
7              case          $600.00
110            case          $260.00
205            case          $312.00
301            item           $42.50
302            item            $4.50
304            box           $280.00
305            case           $48.00
309            case           $40.00
312            box            $72.00

Using the SUBSTRING and SUBSTR Functions

You can use the SUBSTRING and SUBSTR functions to return a portion of a character string. You specify the start position and length (optional) to determine which portion of the character string the function returns.

Using the SUBSTRING Function

You can use the SUBSTRING function to return some portion of a character string. You specify the start position and length (optional) to determine which portion of the character string the function returns. You can specify a positive or negative number for the start position. A start position of 1 specifies that the SUBSTRING function begins from the first position in the string. When the start position is zero (0) or a negative number, the SUBSTRING function counts backward from the beginning of the string.

Figure 225 shows an example of the SUBSTRING function, which returns the first four characters for any sname column values that the query returns. In this example, the SUBSTRING function starts at the beginning of the string and returns four characters counting forward from the start position.

Figure 225. Query

SELECT sname, SUBSTRING(sname FROM 1 FOR 4) FROM state 
   WHERE code = "AZ"

Figure 226. Query Result

sname           (expression)

Arizona         Ariz

In Figure 227, the SUBSTRING function specifies a start position of 6 but does not specify the length. The function returns a character string that extends from the sixth position to the end of the string.

Figure 227. Query

SELECT sname, SUBSTRING(sname FROM 6) FROM state 
   WHERE code = "WV"

Figure 228. Query Result

sname           (expression)

West Virginia   Virginia

In Figure 229, the SUBSTRING function returns only the first character for any sname column value that the query returns. For the SUBSTRING function, a start position of -2 counts backward three positions (0, -1, -2) from the start position of the string (for a start position of 0, the function counts backward one position from the beginning of the string).

Figure 229. Query

SELECT sname, SUBSTRING(sname FROM -2 FOR 4) FROM state 
   WHERE code = "AZ"

Figure 230. Query Result

sname           (expression)

Arizona         A

Using the SUBSTR Function

The SUBSTR function serves the same purpose as the SUBSTRING function, but the syntax of the two functions differs.

To return a portion of a character string, specify the start position and length (optional) to determine which portion of the character string the SUBSTR function returns. The start position that you specify for the SUBSTR function can be a positive or a negative number. However, the SUBSTR function treats a negative number in the start position differently than does the SUBSTRING function. When the start position is a negative number, the SUBSTR function counts backward from the end of the character string, which depends on the length of the string, not the character length of a word or visible characters that the string contains. The SUBSTR function recognizes zero (0) or 1 in the start position as the first position in the string.

Figure 231 shows an example of the SUBSTR function that includes a negative number for the start position. Given a start position of -15, the SUBSTR function counts backward 15 positions from the end of the string to find the start position and then returns the next five characters.

Figure 231. Query

SELECT sname, SUBSTR(sname, -15, 5) FROM state 
   WHERE code = "CA"

Figure 232. Query Result

sname           (expression)

California      Calif

To use a negative number for the start position, you need to know the length of the value that is evaluated. The sname column is defined as CHAR(15), so a SUBSTR function that accepts an argument of type sname can use a start position of 0, 1, or -15 for the function to return a character string that begins from the first position in the string.

Figure 233. Query

SELECT sname, SUBSTR(sname, 1, 5) FROM state 
   WHERE code = "CA

Using the LPAD Function

Use the LPAD function to return a copy of a string that has been left padded with a sequence of characters that are repeated as many times as necessary or truncated, depending on the specified length of the padded portion of the string. Specify the source string, the length of the string to be returned, and the character string to serve as padding.

The data type of the source string and the character string that serves as padding can be any data type that converts to VARCHAR or NVARCHAR.

Figure 234 shows an example of the LPAD function with a specified length of 21 bytes. Because the source string has a length of 15 bytes (sname is defined as CHAR(15)), the LPAD function pads the first six positions to the left of the source string.

Figure 234. Query

SELECT sname, LPAD(sname, 21, "-")
   FROM state
   WHERE code = "CA" OR code = "AZ"

Figure 235. Query Result

sname           (expression)

California      ------California
Arizona         ------Arizona

Using the RPAD Function

Use the RPAD function to return a copy of a string that has been right padded with a sequence of characters that are repeated as many times as necessary or truncated, depending on the specified length of the padded portion of the string. Specify the source string, the length of the string to be returned, and the character string to serve as padding.

The data type of the source string and the character string that serves as padding can be any data type that converts to VARCHAR or NVARCHAR.

Figure 236 shows an example of the RPAD function with a specified length of 21 bytes. Because the source string has a length of 15 bytes (sname is defined as CHAR(15)), the RPAD function pads the first six positions to the right of the source string.

Figure 236. Query

SELECT sname, RPAD(sname, 21, "-")
   FROM state
   WHERE code = "WV" OR code = "AZ"

Figure 237. Query Result

sname           (expression)   
West Virginia   West Virginia  ------
Arizona         Arizona        ------