Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Help with query to search phone numbers
Thu, Aug 11 2022 4:19 PMPermanent Link

Dale Derix

Hi All!

I have a list of phone numbers in lots of different formats. For example:

800-555-1212
8005551212
(800) 555-1212
800.555.1212

Is there a way to write a query that can ignore the dashes, spaces, periods etc. and just search the numbers?

Thanks,

Dale
Fri, Aug 12 2022 12:49 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Dale


If you know what the characters are you could use nested REPLACEs eg

REPLACE(REPLACE(REPLACE(field,'-',''),'.',''),' ','')


Roy Lambert
Fri, Aug 12 2022 6:57 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Dale

A bullet proof solution will be create a function to remove any chars but numbers like below:

CREATE FUNCTION "ONLYNUMBER" (IN "cTxt" VARCHAR COLLATE "ANSI_CI")
RETURNS VARCHAR
BEGIN
    declare cAux varchar default '';
    declare nI integer;
    declare nM integer;
    set nI = 1;
    set nM = Length(cTxt);
    while nI <= nM do
       set cAux = cAux + if(pos(substring(cTxt,nI,1) in '1234567890'),substring(cTxt,nI,1),'');
       set nI = nI + 1;
    end while;
    return cAux;
END;

and use like a regular SQL function

select onlynumber(phone_field) from table
Fri, Aug 12 2022 1:41 PMPermanent Link

Dale Derix

Thanks Roy and Jose.... I will give these a try.

Dale
Sat, Aug 13 2022 2:42 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Dale


There is, in my opinion, a better alternative - format the phone number on entry. In my recruitment app I have these two triggers below for company phone numbers. FormatPhoneNo is a user defined function similar to the one Jose posted, but more complex since it formats the number with spaces to UK STD codes / international codes. The advantage is you end up with a consistent, easier to read format which you can have a calculated column for to strip out separators. eg (01604) 765 870

CREATE TRIGGER "FormatNewPhoneNumbers" BEFORE INSERT ON "Sites"
BEGIN
IF NOT NEWROW._Switchboard IS NULL THEN
IF REPLACE(' ','',NEWROW._Switchboard) = '' THEN
  SET NEWROW._Switchboard = NULL;
ELSE
  SET NEWROW._Switchboard = FormatPhoneNo(NEWROW._Switchboard);
END IF;
END IF;

IF NOT NEWROW._Fax IS NULL THEN
IF REPLACE(' ','',NEWROW._Fax) = '' THEN
  SET NEWROW._Fax = NULL;
ELSE
  SET NEWROW._Fax = FormatPhoneNo(NEWROW._Fax);
END IF;
END IF;

END



CREATE TRIGGER "FormatPhoneNumbers" BEFORE UPDATE OF "_Switchboard", "_Fax" ON "Sites"
BEGIN  
IF NOT NEWROW._Switchboard IS NULL THEN
IF REPLACE(' ','',NEWROW._Switchboard) = '' THEN
  SET NEWROW._Switchboard = NULL;
ELSE
  SET NEWROW._Switchboard = FormatPhoneNo(NEWROW._Switchboard);
END IF;
END IF;

IF NOT NEWROW._Fax IS NULL THEN
IF REPLACE(' ','',NEWROW._Fax) = '' THEN
  SET NEWROW._Fax = NULL;
ELSE
  SET NEWROW._Fax = FormatPhoneNo(NEWROW._Fax);
END IF;
END IF;

END

Roy Lambert
Wed, Oct 19 2022 6:21 AMPermanent Link

Adam Brett

Orixa Systems

Dale,

Note that Elevate is not particularly fast when Functions are called within SELECT statements. Actually it is pretty slow. For this reason, while the function suggested above is great from a technical perspective, from a performance perspective I think it would be poor, unless you are only querying 100s or 1,000s of records.

If you have access to the database structure I would strongly support Roy's suggestion of adding a reformatted telephone number as a new column in the data-table, in such a case the function suggested above could be used, and as the processing would be done on each POST it would be much less noticeable to the users.
Image