Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Help with query to search phone numbers |
Thu, Aug 11 2022 4:19 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 PM | Permanent Link |
Dale Derix | Thanks Roy and Jose.... I will give these a try.
Dale |
Sat, Aug 13 2022 2:42 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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. |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |