LucidDbAppLib CLEAN PHONE
From Eigenpedia
Contents |
Syntax
APPLIB.CLEAN_PHONE ( number_string ) APPLIB.CLEAN_PHONE ( number_string, format_number ) APPLIB.CLEAN_PHONE ( number_string, format_number, flag ) APPLIB.CLEAN_PHONE ( number_string, format_string, flag )
Purpose
Standardizes the format for telephone numbers.
Parameters
- number_string: The phone number to be standardized. You may include non-alphanumeric characters (apart from the '--' string) and letters in the string (expr_s). The non-alphanumeric characters are ignored. The letters are converted into a number from 2 to 9 according to the following table: [varchar]
| Any letter in ... | Converts to ... |
|---|---|
| ABC | 2 |
| DEF | 3 |
| GHI | 4 |
| JKL | 5 |
| MNOP | 6 |
| QRST | 7 |
| UVW | 8 |
| XYZ | 9 |
- format_string: The format to be used for standardization. Use a 9 for each number or letter in the number_string. You can include other letters and non-alphanumeric characters. The default format string is '(999) 999-9999'. [varchar]
- format_number: The format to be used for standardization. [integer]
- 0 is the standard format '999-999-9999'
- 1 is the parenthesis format '(999) 999-9999'
- flag: The default flag is false. [boolean]
- false - return the number_string on error.
- true - give an error message on error.
Output
- [VARCHAR(128)] phone number string in standardized format. Returns null if any inputs are null
Example
A reference table provides the data for the following example(s). The following example specifies no format for one number string, and a mismatch between the second number string and its format.
Values( APPLIB.CLEAN_PHONE( '6458671213'), APPLIB.CLEAN_PHONE('1357','99-9',0) )
The first number is transformed using the default format, the second number is returned unchanged, as follows:
CLEAN_PHONE('6458671213') CLEAN_PHONE('1357', '99-9') (645) 867-1213 1357
The following example specifies a number string, with letters and other characters.
Values( APPLIB.CLEAN_PHONE( 'AGM:867:1213', 'Area Code=999, Number=999-9999',1 ) )
CLEAN_PHONE('AGM:867:1213', 'Area Code=999, Number=999-999', 1) Area Code=246 Number=867-1213
Source Code
http://p4web.eigenbase.org/open/dev/luciddb/src/com/lucidera/luciddb/applib/phone/CleanPhoneUdf.java
