In the following SQL:
SELECT fname, lname, ssn from tab1 where lname like "%vijay%" AND ( telephonenumber IN ((123)456-7890 , ......, ....) OR homephone IN (+123 456-7890, ......, ....... ) OR mobile IN(123-456-7890, ....,.....,....)
Custer wrote an UDR to take out all extra non-digit characters from the telephone number string but the UDR call increasing the query execution time by 6 times. Wondering if this UDR can be improved?
Select using the UDR:
SELECT fname, lname, ssn from tab1 where lname like "%vijay%" AND (sanitize_phonenumber( telephonenumber) IN ((123)456-7890 ) OR (sanitize_phonenumber(homephone) IN (+123 456-7890 ) OR (sanitize_phonenumber(mobile) IN(123-456-7890).....
UDR code:
CREATE FUNCTION sanitize_phonenumber(lvarchar)
RETURNING varchar(255)
WITH(NOT VARIANT, PARALLELIZABLE, PERCALL_COST=50, SELCONST=0.0001)
EXTERNAL NAME "$INFORMIXDIR/extend/ccm/libdbfuncs.so(sanitize_phonenumber)"
LANGUAGE C;
// remove formatting characters from phone number
mi_lvarchar *sanitize_phonenumber(mi_lvarchar *mi_source)
{
int len;
int i, j = 0;
char *pSource = 0, c;
mi_char *s;
mi_lvarchar *ret_string = 0;
s = mi_zalloc(256);
if (s == (mi_char *) NULL)
{
mi_db_error_raise(NULL, MI_EXCEPTION, "mi_alloc failed\n");
return 0; // unreachable, here to satisfy static analysis
}
len = (int) mi_get_varlen(mi_source);
if (len > 0)
{
pSource = mi_get_vardata(mi_source);
}
if (len > 255)
len = 255;
for ( i = 0; i < len; i++)
{
c = tolower(pSource[i]);
switch(c) {
case '0':
case '1':
case '2':
case '3':
case '4':
case '5':
case '6':
case '7':
case '8':
case '9':
case 'a':
case 'b':
case 'c':
case 'd':
case '+':
case ',':
case '*':
case '#':
s[j++] = c;
break;
default:
c = '\0'; // To supress warnings about missing default
}
} // end for
s[j] = '\0'; // terminate
ret_string = mi_new_var(strlen(s));
mi_set_vardata(ret_string, s);
mi_free(s);
return (ret_string);
} // end sanitize_phonenumber
------------------------------
Vijay Lolabattu
------------------------------
#Informix