Using Single Row Functions
You use single row functions when you want to perform an operation on one row at a time and get one row of output for each row. There are five main types of single row functions:
-
Character functions Allow you to manipulate strings of characters and return strings or numbers .
-
Numeric functions Allow you to perform calculations and return numbers.
-
Conversion functions Allow you to convert a value from one data type to another.
-
Date functions Allow you to process dates and times.
-
Regular expression functions Allow you to use regular expressions when searching data. These functions are new for Oracle Database 10 g .
You ll learn about character functions first, followed by numeric functions, conversion functions, and regular expression functions.
| Note | You ll learn about date functions in the next chapter. |
Character Functions
These functions accept character input, which may come from a column in a table or, more generally , from any expression. This input is then processed in some way and a result returned. One example function is UPPER() , which converts all the letters in a character string to uppercase and returns the new string. Another example is NVL() , which converts a null value to a value passed to the function. Table 3-1 shows some of the character functions.
| Function | Description |
|---|---|
| ASCII( x ) | Returns the ASCII value of the character x . |
| CHR( x ) | Returns the character with the ASCII value of x . |
| CONCAT( x , y ) | Appends y to x and then returns the resulting string. |
| INITCAP( x ) | Converts the initial letter of each word in x to uppercase and returns that string. |
| INSTR( x , find _ string [, start ] [, occurrence ]) | Searches for find_string in x and returns the position at which find_string occurs. You can supply an optional start position to begin the search. Also, you can supply an optional occurrence that indicates which occurrence of find_string should be returned. |
| LENGTH( x ) | Returns the number of characters in x . |
| LOWER( x ) | Converts the letters in x to lowercase and returns that string. |
| LPAD( x , width [, pad_string ]) | Pads x with spaces to left, to bring the total length of the string up to width characters. You can supply an optional pad_string , which specifies the string to be repeated to the left of x to fill up the padded space. |
| LTRIM( x [, trim_string ]) | Trims characters from the left of x . You can supply an optional trim_string that specifies the characters to trim; if no trim_string is supplied, spaces are trimmed by default. |
| NANVL( x , value ) | New for Oracle Database 10 g . Returns value if x matches the NaN special value (not a number), otherwise x is returned. |
| NVL( x , value ) | Returns value if x is null; otherwise, x is returned. |
| NVL2( x , value1 , value2 ) | Returns value1 if x is not null; if x is null, value2 is returned. |
| REPLACE( x , search_string , replace_string ) | Searches x for search_string and replaces it with replace_string . |
| RPAD( x , width [, pad_string ]) | Same as LPAD() , but with x padded to the right. |
| RTRIM( x [, trim_string ]) | Same as LTRIM() , but x is trimmed from the right. |
| SOUNDEX( x ) | Returns a string containing the phonetic representation of x . This lets you compare words that are spelled differently, but sound alike in English. |
| SUBSTR( x , start [, length ]) | Returns a substring of x that begins at the position specified by start . An optional length for the substring may be supplied. |
| TRIM([ trim_char FROM) x ) | Trims characters from the left and right of x . You can supply an optional trim_char that specifies the characters to trim; if no trim_char is supplied, spaces are trimmed by default. |
| UPPER( x ) | Converts the letters in x to uppercase and returns that string. |
In Table 3-1 ”and all the syntax definitions that follow ” x and y may represent columns from a table or, more generally, any valid expressions. You ll learn more about some of the functions shown in Table 3-1 in the following sections.
ASCII() and CHR()
You use ASCII( x ) to get the ASCII value of the character x . You use CHR( x ) to get the character with the ASCII value of x .
The following example gets the ASCII value of a, A, z, Z, 0, and 9 using ASCII() :
SELECT ASCII('a'), ASCII('A'), ASCII('z'), ASCII('Z'), ASCII(0), ASCII(9) FROM dual; ASCII('A') ASCII('A') ASCII('Z') ASCII('Z') ASCII(0) ASCII(9) ---------- ---------- ---------- ---------- ---------- ---------- 97 65 122 90 48 57 | Note | The dual table is used in this example. As you saw in the previous chapter, the dual table contains a single row through which you may perform queries that don t go against a particular table. |
The following example gets the characters with the ASCII value of 97, 65, 122, 90, 48, and 57 using CHR() :
SELECT CHR(97), CHR(65), CHR(122), CHR(90), CHR(48), CHR(57) FROM dual; C C C C C C - - - - - - a A z Z 0 9
Notice the characters returned from CHR() in this example are the same as those passed to ASCII() in the previous example. This shows that CHR() and ASCII() have the opposite effect.
CONCAT()
You use CONCAT( x , y ) to append y to x . CONCAT() then returns the resulting string.
The following example selects the first_name and last_name columns from the products table, appending last_name to first_name using CONCAT() :
SELECT CONCAT(first_name, last_name) FROM customers; CONCAT(FIRST_NAME,LA -------------------- JohnBrown CynthiaGreen SteveWhite GailBlack DoreenBlue
| Note | The CONCAT() function is the same as the operator you saw in the previous chapter. |
INITCAP()
You use INITCAP( x ) to convert the initial letter of each word in x to uppercase.
The following example selects the product_id and description columns from the products table, and uses INITCAP() to convert the first letter of each word in description to uppercase:
SELECT product_id, INITCAP(description) FROM products WHERE product_id < 4; PRODUCT_ID INITCAP(DESCRIPTION) ---------- ------------------------------- 1 A Description Of Modern Science 2 Introduction To Chemistry 3 A Star Explodes
INSTR()
You use INSTR( x , find_string [, start ] [, occurrence ]) to search for find_string in x . INSTR() returns the position at which find_string occurs. The optional start position allows you to specify the position to start the search in x . You can also supply an optional occurrence that indicates which occurrence of find_string should be returned.
The following example selects the name column from the products table and displays the position where the string Science occurs in the name column for product #1:
SELECT name, INSTR(name, 'Science') FROM products WHERE product_id = 1; NAME INSTR(NAME,'SCIENCE') ------------------------------ --------------------- Modern Science 8
The next example displays the position where the second occurrence of e occurs, starting from the beginning of the product name using INSTR() :
SELECT name, INSTR(name, 'e', 1, 2) FROM products WHERE product_id = 1; NAME INSTR(NAME,'E',1,2) ------------------------------ ------------------- Modern Science 11
Notice the second e in Modern Science is the eleventh character.
You can also use dates in character functions. The following example displays the position where the string JAN occurs in dob for customer #1 using INSTR() :
SELECT customer_id, dob, INSTR(dob, 'JAN') FROM customers WHERE customer_id = 1; CUSTOMER_ID DOB INSTR(DOB,'JAN') ----------- --------- ---------------- 1 01-JAN-65 4
LENGTH()
You use LENGTH( x ) to get the number of characters in x . The following example displays the length of the strings in the name column of the products table using LENGTH() :
SELECT name, LENGTH(name) FROM products; NAME LENGTH(NAME) ------------------------------ ------------ Modern Science 14 Chemistry 9 Supernova 9 Tank War 8 Z Files 7 2412: The Return 16 Space Force 9 13 From Another Planet 19 Classical Music 15 Pop 3 5 Creative Yell 13 My Front Line 13
The next example displays the total number of characters that make up the product price using LENGTH(); notice that the decimal point (.) is counted in the number of price characters:
SELECT price, LENGTH(price) FROM products WHERE product_id < 3; PRICE LENGTH(PRICE) ---------- ------------- 19.95 5 30 2
LOWER() and UPPER()
You use LOWER( x ) to convert the letters in x to lowercase. Similarly, you use UPPER( x ) to convert the letters in x to uppercase.
The following example selects the first_name and last_name columns from the customers table and converts them to uppercase using the UPPER() function, and the strings in the last_name column are converted to lowercase using the LOWER() function:
SELECT UPPER(first_name), LOWER(last_name) FROM customers; UPPER(FIRS LOWER(LAST ---------- ---------- JOHN brown CYNTHIA green STEVE white GAIL black DOREEN blue
LPAD() and RPAD()
You use LPAD( x , width [, pad_string ]) to pad x with spaces to left to bring the total length of the string up to width characters. If a string is supplied in pad_string , this string is repeated to the left to fill up the padded space. The resulting padded string is then returned. Similarly, you use RPAD( x , width [, pad_string ]) to pad x with strings to the right.
The following example selects the name and price columns from the products table. The name column is right-padded using RPAD() to a length of 30 characters, with periods filling up the padded space. The price column is left-padded using LPAD() to a length of 8, with the string *+ filling up the padded space.
SELECT RPAD(name, 30, '.'), LPAD(price, 8, '*+') FROM products WHERE product_id < 4; RPAD(NAME,30,'.') LPAD(PRI ------------------------------ -------- Modern Science................ *+*19.95 Chemistry..................... *+*+*+30 Supernova..................... *+*25.99
| Note | This example shows that you can use some of the character functions using numbers. Specifically, the price column in the example contains a number and was left padded by LPAD() . |
LTRIM(), RTRIM(), and TRIM()
You use LTRIM( x [, trim_string ]) to trim characters from the left of x . You can supply an optional trim_string that specifies the characters to trim; if no trim_string is supplied, spaces are trimmed by default. Similarly, you use RTRIM() to trim characters from the right of x . You use TRIM() to trim characters from the left and right of x . For example:
SELECT LTRIM(' Hello Gail Seymour!'), RTRIM('Hi Doreen Oakley!abcabc', 'abc'), TRIM('0' FROM '000Hey Steve Button!00000') FROM dual; LTRIM('HELLOGAILSEY RTRIM('HIDOREENOA TRIM('0'FROM'000H ------------------- ----------------- ----------------- Hello Gail Seymour! Hi Doreen Oakley! Hey Steve Button! NVL()
You use NVL() to convert a null to a known value. NVL ( x , value ) returns value if x is null; otherwise, x is returned.
The following example selects the customer_id and phone columns from the customers table. Null values for the phone column are converted to the string Unknown Phone Number by NVL() :
SELECT customer_id, NVL(phone, 'Unknown Phone Number') FROM customers; CUSTOMER_ID NVL(PHONE,'UNKNOWNPH ----------- -------------------- 1 800-555-1211 2 800-555-1212 3 800-555-1213 4 800-555-1214 5 Unknown Phone Number
NVL2()
NVL2( x , value1 , value2 ) returns value1 if x is not null. If x is null, value2 is returned.
The following example selects the customer_id and phone columns from the customers table. Not null values for the phone column are converted to the string Known and null values are converted to Unknown by NVL2() :
SELECT customer_id, NVL2(phone, 'Known', 'Unknown') FROM customers; CUSTOMER_ID NVL2(PH ----------- ------- 1 Known 2 Known 3 Known 4 Known 5 Unknown
Notice that the phone column values are converted to Known for customers #1 through #4 because the phone column values for those rows are not null. For customer #5 the phone column value is converted to Unknown because the phone column is null for that row.
REPLACE()
You use REPLACE( x , search_string , replace_string ) to search x for search_string and replace it with replace_string .
The following example retrieves the name column from the products table for product #1 (whose name is Modern Science ) and replaces the string Science with Physics using REPLACE() :
SELECT REPLACE(name, 'Science', 'Physics') FROM products WHERE product_id = 1; REPLACE(NAME,'SCIENCE','PHYSICS') ----------------------------------------------- Modern Physics
The name for product #1 is Modern Science and so the example returns Modern Physics.
| Note | REPLACE() doesn t modify the actual row in the database, only the returned row in the result set. |
SOUNDEX()
You use SOUNDEX( x ) to get a string containing the phonetic representation of x . This lets you compare words that are spelled differently but sound alike in English.
The following example selects the first_name and last_name columns from the customers table where last_name sounds like whyte using SOUNDEX() :
SELECT first_name, last_name FROM customers WHERE SOUNDEX(last_name) = SOUNDEX('whyte'); FIRST_NAME LAST_NAME ---------- ---------- Steve White The next example does the same thing as the previous one except the string bloo is passed to SOUNDEX() :
SELECT first_name, last_name FROM customers WHERE SOUNDEX(last_name) = SOUNDEX('bloo'); FIRST_NAME LAST_NAME ---------- ---------- Doreen Blue SUBSTR()
You use SUBSTR( x , start [, length ]) to return a substring of x that begins at the position specified by start . You can also provide an optional length for the substring.
The following example uses SUBSTR() to select a seven-character substring starting at position 2 from the name column of the products table:
SELECT SUBSTR(name, 2, 7) FROM products WHERE product_id < 4; SUBSTR(------- odern S hemistr upernov
Using Expressions with Functions
You re not limited to just using columns in functions: you can supply any valid expression that evaluates to a string. The following example uses the SUBSTR() function to select the substring little from the string Mary had a little lamb:
SELECT SUBSTR('Mary had a little lamb', 12, 6) FROM dual; SUBSTR ------ little Combining Functions
You can use any valid combination of functions in a SQL statement. The following example combines the UPPER() and SUBSTR() functions; notice that the output from SUBSTR() is passed to UPPER() :
SELECT name, UPPER(SUBSTR(name, 2, 8)) FROM products WHERE product_id < 4; NAME UPPER(SU ------------------------------ -------- Modern Science ODERN SC Chemistry HEMISTRY Supernova UPERNOVA
| Note | This ability to combine functions is not limited to character functions. Any valid combination of functions will work. |
Numeric Functions
You use the numeric functions to perform calculations. These functions accept an input number, which may come from a column in a table or from an expression that evaluates to a number. A calculation is performed with this input and a number is returned. An example of a numeric function is SQRT() , which returns the square root of the input number.
| Note | Some numeric functions, strictly speaking, don t actually perform a calculation. Some just return some attribute of the input number. For example, SIGN() returns “1 if the input number is negative, 1 if the input number is positive, or zero if the input number is 0. |
Table 3-2 shows some of the numeric functions.
| Function | Description | Examples |
|---|---|---|
| ABS( x ) | Returns the absolute value of x . | ABS(10) = 10 |
| ACOS( x ) | Returns the arccosine of x . | ACOS(1) = 0 |
| ASIN( x ) | Returns the arcsine of x . | ASIN(1) = 1.57079633 |
| ATAN( x ) | Returns the arctangent of x . | ATAN(1) = .785398163 |
| ATAN2( x , y ) | Returns the arctangent of x and y . | ATAN2(1, -1) = 2.35619449 |
| BITAND( x , y ) | Returns the result of performing a bitwise AND on x and y . | > BITAND(0, 0) = 0 |
| COS( x ) | Returns the cosine of x , where x is an angle in radians. | COS(90 * 3.1415926) = 1 |
| COSH( x ) | Returns the hyperbolic cosine of x . | COSH(3.1415926) = 11.5919527 |
| CEIL( x ) | Returns the smallest integer greater than or equal to x . | CEIL(5.8) = 6 |
| EXP( x ) | Returns the result of the number e raised to the power x , where e is approximately 2.71828183. | EXP(1) = 2.71828183 |
| FLOOR( x ) | Returns the largest integer less than or equal to x . | FLOOR(5.8) = 5 |
| LOG( x , y ) | Returns the logarithm, base x , of y . | LOG(2, 4) = 2 |
| LN( x ) | Returns the natural logarithm of x . | LN(2.71828183) = 1 |
| MOD( x , y ) | Returns the remainder when x is divided by y . | MOD(8, 3) = 2 |
| POWER( x , y ) | Returns the result of x raised to the power y . | POWER(2, 1) = 2 |
| ROUND( x [, y ]) | Returns the result of rounding x an optional y decimal places. If y is omitted, x is rounded to zero decimal places. If y is negative, x is rounded to the left of the decimal point. | ROUND(5.75) = 6 |
| SIGN( x ) | Returns -1 if x is negative, 1 if x is positive, or 0 if x is zero. | SIGN(-5) = -1 |
| SIN( x ) | Returns the sine of x . | SIN(0) = 0 |
| SINH( x ) | Returns the hyperbolic sine of x . | SINH(1) = 1.17520119 |
| SQRT( x ) | Returns the square root of x . | SQRT(25) = 5 |
| TAN( x ) | Returns the tangent of x . | TAN(0) = 0 |
| TANH( x ) | Returns the hyperbolic tangent of x . | TANH(1) = .761594156 |
| TRUNC( x [, y ]) | Returns the result of truncating x an optional y decimal places. If y is omitted, x is truncated to zero decimal places. If y is negative, x is truncated to the left of the decimal point. | TRUNC(5.75) = 5 |
You ll learn more about some of the functions shown in Table 3-2 in the following sections.
ABS()
You use ABS( x ) to get the absolute value of x . The absolute value of a number is that number without any positive or negative sign. The following example displays the absolute value of 10 and - 10:
SELECT ABS(10), ABS(-10) FROM dual; ABS(10) ABS(-10) ---------- ---------- 10 10
As you can see, the absolute value of 10 is 10; this is because there is no sign explicitly associated with it. The absolute value of -10 is 10 because the negative sign is dropped.
Of course, the parameters input to any of the number functions don t have to be literal numbers. The input may also be columns from a table or, more generally, any valid expression. The following example displays the absolute value of subtracting 30 from the price column from the products table for the first three products:
SELECT product_id, price, price 30, ABS(price - 30) FROM products WHERE product_id < 4; PRODUCT_ID PRICE PRICE-30 ABS(PRICE-30) ---------- ---------- ---------- ------------- 1 19.95 -10.05 10.05 2 30 0 0 3 25.99 -4.01 4.01
CEIL()
You use CEIL( x ) to get the smallest integer greater than or equal to x . The following example uses CEIL() to display the absolute value of 5.8 and -5.2, respectively:
SELECT CEIL(5.8), CEIL(-5.2) FROM dual; CEIL(5.8) CEIL(-5.2) ---------- ---------- 6 -5
The ceiling for 5.8 is 6; this is because 6 is the smallest integer greater than 5.8. The ceiling for -5.2 is -5; this is because -5.2 is negative, and the smallest integer greater than this is -5.
FLOOR()
You use FLOOR( x ) to get the largest integer less than or equal to x . The following example uses FLOOR() to display the absolute value of 5.8 and -5.2, respectively:
SELECT FLOOR(5.8), FLOOR(-5.2) FROM dual; FLOOR(5.8) FLOOR(-5.2) ---------- ----------- 5 -6
The floor for 5.8 is 5; this is because 5 is the largest integer less than 5.8. The floor for -5.2 is -6; this is because -5.2 is negative, and the largest integer less than this is -6.
MOD()
You use MOD( x , y ) to get the remainder when x is divided by y . The following example uses MOD() to display the remainder when 8 is divided by 3 and 4, respectively:
SELECT MOD(8, 3), MOD(8, 4) FROM dual; MOD(8,3) MOD(8,4) ---------- ---------- 2 0
The remainder when 8 is divided by 3 is 2; this is because 3 goes into 8 twice, leaving 2 left over ”the remainder. The remainder when 8 is divided by 4 is 0; this is because 4 goes into 8 twice, leaving nothing left over.
POWER()
You use POWER( x , y ) to get the result of x raised to the power y . The following example uses POWER() to display 2 raised to the power 1 and 3, respectively:
SELECT POWER(2, 1), POWER(2, 3) FROM dual; POWER(2,1) POWER(2,3) ---------- ---------- 2 8
When 2 is raised to the power 1, this is equivalent to 2*1, the result is 2; 2 raised to the power 3 is equivalent to 2*2*2, the result of which is 8.
ROUND()
You use ROUND( x , [ y ]) to get the result of rounding x an optional y decimal places. If y is omitted, x is rounded to zero decimal places. If y is negative, x is rounded to the left of the decimal point.
The following example uses ROUND() to display the result of rounding 5.75 to zero, 1, and -1 decimal places, respectively:
SELECT ROUND(5.75), ROUND(5.75, 1), ROUND(5.75, -1) FROM dual; ROUND(5.75) ROUND(5.75,1) ROUND(5.75,-1) ----------- ------------- -------------- 6 5.8 10
5.75 rounded to zero decimal places is 6. 5.75 rounded to one decimal place (to the right of the decimal point) is 5.8. 5.75 rounded to one decimal place (to the left of the decimal point, as indicated using a negative sign) is 10.
SIGN()
You use SIGN( x ) to get the sign of x . SIGN() returns -1 if x is negative, 1 if x is positive, or 0 if x is zero. The following example displays the sign of -5, 5, and 0, respectively:
SELECT SIGN(-5), SIGN(5), SIGN(0) FROM dual; SIGN(-5) SIGN(5) SIGN(0) ---------- ---------- ---------- -1 1 0
The sign of -5 is -1; the sign of 5 is 1; the sign of 0 is 0.
SQRT()
You use SQRT( x ) to get the square root of x . The following example displays the square root of 25 and 5, respectively:
SELECT SQRT(25), SQRT(5) FROM dual; SQRT(25) SQRT(5) ---------- ---------- 5 2.23606798
The square root of 25 is 5; the square root of 5 is approximately 2.236.
TRUNC()
You use TRUNC( x , [ y ]) to get the result of truncating the number x to an optional y decimal places. If y is omitted, x is truncated to zero decimal places. If y is negative, x is truncated to the left of the decimal point. The following example displays truncating 5.75 to zero, 1, and - 1 decimal places:
SELECT TRUNC(5.75), TRUNC(5.75, 1), TRUNC(5.75, -1) FROM dual; TRUNC(5.75) TRUNC(5.75,1) TRUNC(5.75,-1) ----------- ------------- -------------- 5 5.7 0
As you can see from this example, 5.75 truncated to zero decimal places is 5. 5.75 truncated to one decimal place (to the right of the decimal point) is 5.7. 5.75 truncated to one decimal place (to the left of the decimal point, as indicated using a negative sign) is 0.
Conversion Functions
Sometimes you might want to convert a value from one data type to another. For example, you might want to reformat the price of a product that is stored as a number (1346.95, for example) to string containing dollar signs and commas ($1,346.95). You can use a conversion function to convert a value from one data type to another.
Table 3-3 shows some of the conversion functions.
| Function | Description |
|---|---|
| ASCIISTR( x ) | Converts x to an ASCII string, where x may be a string in any character set. |
| BIN_TO_NUM( x ) | Converts x to a binary number. Returns a NUMBER . |
| CAST( x AS type_name ) | Converts a value in x from one data type to another specified in type_name . |
| CHARTOROWID( x ) | Converts x to a ROWID . |
| COMPOSE( x ) | Converts x to a Unicode string in its fully normalized form in the same character set as x . Unicode uses a 2-byte character set and can represent over 65,000 characters; it may also be used to represent non-English characters. |
| CONVERT( x , source_char_set , dest_char_set ) | Converts x from source_char_set to dest_char_set . |
| DECODE( x , search , result , default ) | Compares x with the value in search; if equal, DECODE() returns search , otherwise the value in default is returned. |
| DECOMPOSE( x ) | Converts x to a Unicode string after decomposition in the same character set as x . |
| HEXTORAW( x ) | Converts the character x containing hexadecimal digits (base 16) to a binary number ( RAW ). This function returns the returns RAW number. |
| NUMTODSINTERVAL( x ) | Converts the number x to an INTERVAL DAY TO SECOND . You'll learn about date and time interval “ related functions in the next chapter. |
| NUMTOYMINTERVAL( x ) | Convert the number x to an INTERVAL YEAR TO MONTH . |
| RAWTOHEX( x ) | Converts the binary number ( RAW ) x to a VARCHAR2 character containing the equivalent hexadecimal number. |
| RAWTONHEX( x ) | Converts the binary number ( RAW ) x to an NVARCHAR2 character containing the equivalent hexadecimal number. An NVARCHAR2 is used to store strings in the national character set. |
| ROWIDTOCHAR( x ) | Converts the ROWID x to a VARCHAR2 character. |
| ROWIDTONCHAR( x ) | Converts the ROWID x to an NVARCHAR2 character. |
| TO_BINARY_DOUBLE( x ) | New for Oracle Database 10 g . Converts x to a BINARY_DOUBLE . |
| TO_BINARY_FLOAT( x ) | New for Oracle Database 10 g . Converts x to a BINARY_FLOAT . |
| TO_CHAR( x [, format ]) | Converts x to a VARCHAR2 string. You can supply an optional format that indicates the format of x . |
| TO_CLOB( x ) | Converts x to a character large object ( CLOB ). A CLOB is used to store large amounts of character data. |
| TO_DATE( x [, format ]) | Converts x to a DATE . |
| TO_DSINTERVAL( x ) | Convert the string x to an INTERVAL DAY TO SECOND . |
| TO_MULTI_BYTE( x ) | Converts the single-byte characters in x to their corresponding multi-byte characters. The return type is the same as the type for x . |
| TO_NCHAR( x ) | Converts x in the database character set to an NVARCHAR2 . |
| TO_NCLOB( x ) | Converts x to an NCLOB . An NCLOB is used to store large amounts of national language character data. |
| TO_NUMBER( x [, format ]) | Converts x to a NUMBER . |
| TO_SINGLE_BYTE( x ) | Converts the multi-byte characters in x to their corresponding single-byte characters. The return type is the same as the type for x . |
| TO_TIMESTAMP( x ) | Converts the string x to a TIMESTAMP . |
| TO_TIMESTAMP_TZ( x ) | Converts the string x to a TIMESTAMP WITH TIME ZONE . |
| TO_YMINTERVAL( x ) | Converts the string x to an INTERVAL YEAR TO MONTH . |
| TRANSLATE( x , from_string , to_string ) | Converts all occurrences of from_string in x to to_string . |
| UNISTR( x ) | Converts the characters in x to the national language character set ( NCHAR ). |
You ll learn more about the TO_CHAR() and TO_NUMBER() functions in the following sections. You ll learn about some of the other functions in Table 3-3 as you progress through this book.
TO_CHAR()
You use TO_CHAR( x [, format ]) to convert x to a string. You can also provide an optional format that indicates the format of x . The structure format depends on whether x is a number or date.
| Note | You ll learn how to use TO_CHAR() to convert a number to a string in this section, and see how to convert a date to a string in the next chapter. |
Let s take a look at a couple of simple examples that use TO_CHAR() to convert a number to a string. The following example converts 12345.67 to a string:
SELECT TO_CHAR(12345.67) FROM dual; TO_CHAR(1 --------- 12345.67
The next example uses TO_CHAR() to convert 12345678.90 to a string and specifies this number is to be converted using the format 99,999.99 . This results in the string returned by TO_CHAR() having a comma to delimit the thousands:
SELECT TO_CHAR(12345.67, '99,999.99') FROM dual; TO_CHAR(12 ---------- 12,345.67
The optional format string you may pass to TO_CHAR() has a number of parameters that affect the string returned by TO_CHAR() . Some of these parameters are listed in Table 3-4.
| Parameter | Format Examples | Description |
|---|---|---|
| 9 | 999 | Returns digits in specified positions with leading negative sign if the number is negative. |
|
| 0999 | 0999 : Returns a number with leading zeros. |
| . | 999.99 | Returns a decimal point in the specified position. |
| , | 9,999 | Returns a comma in the specified position. |
| $ | $999 | Returns a leading dollar sign. |
| B | B9.99 | If the integer part of a fixed point number is zero, returns spaces for the zeros. |
| C | C999 | Returns the ISO currency symbol in the specified position. The symbol comes from the NLS_ISO_CURRENCY parameter. |
| D | 9D99 | Returns the decimal point symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter (default is a period character). |
| EEEE | 9.99EEEE | Returns number using the scientific notation. |
| FM | FM90.9 | Removes leading and trailing spaces from number. |
| G | 9G999 | Returns the group separator symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter. |
| L | L999 | Returns the local currency symbol in the specified position. The symbol comes from the NLS_CURRENCY parameter. |
| MI | 999MI | Returns a negative number with a trailing minus sign. Returns a positive number with a trailing space. |
| PR | 999PR | Returns a negative number in angle brackets (< >). Returns a positive number with leading and trailing spaces. |
| RN | RN | Returns number as Roman numerals. RN returns uppercase numerals; rn returns lowercase numerals. Number must be an integer between 1 and 3999. |
| S | S999 | S999 : Returns a negative number with a leading negative sign; returns a positive number with a leading positive sign. 999S : Returns a negative number with a trailing negative sign; returns a positive number with a trailing positive sign. |
| TM | TM | Returns a number using the minimum number of characters. Default is TM9 , which returns the number using fixed notation unless the number of characters is greater than 64. If greater than 64, the number is returned using scientific notation. |
| U | U999 | Returns the dual currency symbol (Euro, for example) in the specified position. The symbol comes from the NLS_DUAL_CURRENCY parameter. |
| V | 99V99 | Returns number multiplied by 10 x where x is the number of 9 characters after the V . If necessary, the number is rounded. |
| X | XXXX | Returns the number in hexadecimal. If the number is not an integer, the number is rounded to an integer. |
Let s look at some more examples of converting numbers to strings using TO_CHAR() . The following table shows examples of calling TO_CHAR() along with the output returned from TO_CHAR() .
| TO_CHAR() Function Call | Output |
|---|---|
| TO_CHAR(12345.67, ˜99999.99) | 12345.67 |
| TO_CHAR(12345.67, ˜99,999.99) | 12,345.67 |
| TO_CHAR(-12345.67, ˜99,999.99) | -12,345.67 |
| TO_CHAR(12345.67, ˜099,999.99) | 012,345.67 |
| TO_CHAR(12345.67, ˜99,999.9900) | 12,345.6700 |
| TO_CHAR(12345.67, ˜$99,999.99) | $12,345.67 |
| TO_CHAR(0.67, ˜B9.99) | .67 |
| TO_CHAR(12345.67, ˜C99,999.99) | USD12,345.67 |
| TO_CHAR(12345.67, ˜99999D99) | 12345.67 |
| TO_CHAR(12345.67, ˜99999.99EEEE) | 1.23E+04 |
| TO_CHAR(0012345.6700, ˜FM99999.99) | 12345.67 |
| TO_CHAR(12345.67, ˜99999G99) | 123,46 |
| TO_CHAR(12345.67, ˜L99,999.99) | $12,345.67 |
| TO_CHAR(-12345.67, ˜99,999.99MI) | 12,345.67- |
| TO_CHAR(-12345.67, ˜99,999.99PR) | < 12,345.67 > |
| TO_CHAR(2007, ˜RN) | MMVII |
| TO_CHAR(12345.67, ˜TM) | 12345.67 |
| TO_CHAR(12345.67, ˜U99,999.99) | $12,345.67 |
| TO_CHAR(12345.67, ˜99999V99) | 1234567 |
TO_CHAR() will return a string of pound characters ( # ) if you try and format a number that contains too many digits for the format you have provided. For example:
SELECT TO_CHAR(12345678.90, '99,999.99') FROM dual; TO_CHAR(12 ---------- ##########
Pound characters are returned by TO_CHAR() because the number 12345678.90 has more digits than those allowed in the format string 99,999.99 .
You can, of course, use TO_CHAR() to convert columns containing numbers to strings. For example, the following query uses TO_CHAR() to convert the price column of the products table to a string:
SELECT product_id, 'The price of this product is' TO_CHAR(price, '.99') FROM products WHERE product_id < 5; PRODUCT_ID 'THEPRICEOFTHISPRODUCTIS'TO_CHAR(---------- ----------------------------------- 1 The price of this product is .95 2 The price of this product is .00 3 The price of this product is .99 4 The price of this product is .95
TO_NUMBER()
You use TO_NUMBER( x [, format ]) to convert x to a number. You can provide an optional format string to indicate the format of x . Your format string may use the same parameters as those listed earlier in Table 3-4.
The following example converts the string 970.13 to a number using TO_NUMBER() :
SELECT TO_NUMBER('970.13') FROM dual; TO_NUMBER('970.13') ------------------- 970.13 The next example converts the string 970.13 to a number using TO_NUMBER() and then adds 25.5 to that number:
SELECT TO_NUMBER('970.13') + 25.5 FROM dual; TO_NUMBER('970.13')+25.5 ------------------------ 995.63 The next example converts the string -$12,345.67 to a number, passing the format string $99,999.99 to TO_NUMBER() :
SELECT TO_NUMBER('-,345.67', ',999.99') FROM dual; TO_NUMBER('-,345.67',',999.99') ------------------------------------- -12345.67 Regular Expression Functions
In this section, you ll learn about regular expression functions, which extend the search capabilities for strings and allow you to search for a specified set of characters or pattern of characters.
A regular expression is a pattern you use to match against a string. For example, let s say you have the following series of years :
1965 1968 1971 1970
Say you want to get the years between 1965 and 1968 inclusive. You can do that using the following regular expression:
^196[5-8]$
The regular expression contains a number of metacharacters . In this example, ^ , [5-8] , and $ are the metacharacters. ^ matches the beginning position of a string; [5-8] matches characters between 5 and 8; $ matches the end position of a string. Therefore ^196 matches a string that begins with 196, and [5-8]$ matches a string that ends with 5, 6, 7, or 8. So ^196[5-8]$ matches 1965, 1966, 1967, and 1968, which is the required result.
In the next example, assume you have the following string that contains a quote from Shakespeare s Romeo and Juliet :
But, soft! What light through yonder window breaks?
Let s say you want to get the substring light . You can do this by applying the following regular expression to the quote string:
l[[:alpha:]]{4} In this example, [[:alpha:]] and {4} are the metacharacters. [[:alpha:]] matches an alphanumeric character A-Z and a-z; {4} repeats the previous match four times. When l , [[:alpha:]] , and {4} are combined, they match a sequence of five letters starting with l . Therefore, when the regular expression l[[:alpha:]]{4} is applied to the string, the substring light is matched.
Table 3-5 lists some of the metacharacters you can use in a regular expression, along with their meaning and a simple example of their use.
| Metacharacters | Meaning | Examples |
|---|---|---|
| \ | Indicates that the match character is a special character, a literal, or a backreference. (A backreference repeats the previous match.) | \n matches the newline character \\ matches \ \( matches ( \) matches ) |
| ^ | Matches the position at the start of the string. | ^A matches A if A is the first character in the string. |
| $ | Matches the position at the end of the string. | $B matches B if B is the last character in the string. |
| * | Matches the preceding character zero or more times. | ba*rk matches brk , bark , baark , and so on. |
| + | Matches the preceding character one or more times. | ba+rk matches bark , baark , and so on, but not brk . |
| ? | Matches the preceding character zero or one time. | ba?rk matches brk and bark only. |
| {n} | Matches a character exactly n times, where n is an integer. | hob{2} it matches hobbit . |
| {n,m} | Matches a character at least n times and at most m times, where n and m are both integers. | hob{2,3} it matches hobbit and hobbbit only. |
| . | Matches any single character except null. | hob.it matches hobait , hobbit , and so on. |
| (pattern) | A subexpression that matches the specified pattern. You use subexpressions to build up complex regular expressions. You can access the individual matches, known as captures, from this type of subexpression. | anatom(yies) matches anatomy and anatomies . |
| xy | Matches x or y , where x and y are one or more characters. | war peace matches war or peace . |
| [abc] | Matches any of the enclosed characters. | [ab]bc matches abc and bbc . |
| [a-z] | Matches any character in the specified range. | [a-c]bc matches abc , bbc , and cbc . |
| [: :] | Specifies a character class and matches any character in that class. | [:alphanum:] matches alphanumeric characters 0-9, A-Z, and a-z. [:alpha:] matches alphabetic characters A-Z and a-z. [:blank:] matches space or tab. [:digit:] matches digits 0-9. [:graph:] matches non-blank characters. [:lower:] matches lowercase alphabetic characters a-z. [:print:] is similar to [:graph:] except [:print:] includes the space character. [:punct:] matches punctuation characters . , ˜, and so on. [:space:] matches all whitespace characters. [:upper:] matches all uppercase alphabetic characters A-Z. [:xdigit:] matches characters permissible in a hexadecimal number 0-9, A-F, and a-f. |
| [..] | Matches one collation element, like a multicharacter element. | No example. |
| [==] | Specifies equivalence classes. | No example. |
| \n | This is a backreference to an earlier capture, where n is a positive integer. | (.)\1 matches two consecutive identical characters. The (.) captures any single character except null, and the \1 repeats the capture, matching the same character again, therefore matching two consecutive identical characters. |
Table 3-6 shows the regular expression functions available in Oracle Database 10 g . You ll learn more about the regular expression functions in the following sections.
| Function | Description |
|---|---|
| REGEXP_LIKE( x , pattern [, match_option ]) | Returns true when the source x matches the regular expression pattern . You can change the default matching using match_option , which may be set to:
|
| REGEXP_INSTR( x , pattern [, start [, occurrence [, return_option [, match_option ]]]]) | Searches for pattern in x and returns the position at which pattern occurs. You can supply an optional:
|
| REGEXP_REPLACE( x , pattern [, replace_string [, start [, occurrence [, match_option ]]]]) | Searches x for pattern and replaces it with replace_string . The other options have the same meaning as those shown earlier. |
| REGEXP_SUBSTR( x , pattern [, start [, occurrence [, match_option ]]]) | Returns a substring of x that matches pattern , which begins at the position specified by start . The other options have the same meaning as those shown earlier. |
REGEXP_LIKE()
You use REGEXP_LIKE( x , pattern [, match_option ]) to search x for the regular expression pattern . You can provide an optional match_option string to indicate the default matching.
The following example retrieves customers whose date of birth is between 1965 and 1968 using REGEXP_LIKE() :
SELECT customer_id, first_name, last_name, dob FROM customers WHERE REGEXP_LIKE(TO_CHAR(dob, 'YYYY'), '^196[5-8]$'); CUSTOMER_ID FIRST_NAME LAST_NAME DOB ----------- ---------- ---------- --------- 1 John Brown 01-JAN-65 2 Cynthia Green 05-FEB-68
The next example retrieves customers whose first name starts with J or j . Notice the regular expression passed to REGEXP_LIKE() is ^j and the match option is i , which indicates case insensitive matching and so ^j matches J or j :
SELECT customer_id, first_name, last_name, dob FROM customers WHERE REGEXP_LIKE(first_name, '^j', 'i'); CUSTOMER_ID FIRST_NAME LAST_NAME DOB ----------- ---------- ---------- --------- 1 John Brown 01-JAN-65
REGEXP_INSTR()
You use REGEXP_INSTR( x , pattern [, start [, occurrence [, return_option [, match_option ]]]]) to search for pattern in x ; REGEXP_INSTR() returns the position at which pattern occurs. The position starts at number 1.
The following example returns the position that matches the regular expression l[[:alpha:]] {4} using REGEXP_INSTR() :
SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}') AS result FROM dual; RESULT ---------- 17 Notice 17 is returned, which is the position of the l in light .
The next example returns the position of the second occurrence that matches the regular expression s[[:alpha:]]{3} starting at position 1:
SELECT REGEXP_INSTR('But, soft! What light through yonder window softly breaks?', 's[[:alpha:]]{3}', 1, 2) AS result FROM dual; RESULT ---------- 45 The next example returns the position of the second occurrence that matches the letter o starting at position 10 using REGEXP_INSTR() :
SELECT REGEXP_INSTR('But, soft! What light through yonder window breaks?', 'o', 10, 2) AS result FROM dual; RESULT ---------- 32 REGEXP_REPLACE()
You use REGEXP_REPLACE( x , pattern [, replace_string [, start [, occurrence [, match_option ]]]]) to search x for pattern and replace it with replace_string .
The following example replaces the substring that matches the regular expression l[[: alpha:]]{4} with the string sound using REGEXP_REPLACE() :
SELECT REGEXP_REPLACE('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}', 'sound') AS result FROM dual; RESULT --------------------------------------------------- But, soft! What sound through yonder window breaks? Notice light has been replaced by sound .
REGEXP_SUBSTR()
You use REGEXP_SUBSTR( x , pattern [, start [, occurrence [, match_option ]]]) to get a substring of x that matches pattern , which begins at the position specified by start .
The following example returns the substring that matches the regular expression l[[:alpha:]] {4} using REGEXP_SUBSTR() :
SELECT REGEXP_SUBSTR('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}') AS result FROM dual; RESUL ----- light 
EAN: 2147483647
Pages: 217