AnsweredAssumed Answered

ExecuteSQL - equivalent to FM Filter/substitute function

Question asked by fmdataweb on Sep 29, 2017
Latest reply on Oct 5, 2017 by BenGraham

I'm trying to use the ExecuteSQL function to find any records that match a phone number. The problem is that the phone numbers I'm trying to locate are entered with spaces for readability, e.g. '0412 345 678', but the number I'm comparing them against is entered with no spaces, e.g. '0412345678'.

 

My ExecuteSQL statement currently looks like this:

 

ExecuteSQL ( "Select ID from Contacts where PhoneMobile = ?" ; "" ; "" ; Callers::Phone )

 

I know I can replace the PhoneMobile field with another field that uses the Filter or Subtsitute functions to remove any spaces etc, and this does work, but I'm going to be comparing this against multiple phone fields so trying to avoid adding any unnecessary fields to my schema. I'm trying to find an equivalent to the FM Filter/Substitute functions but from looking at the FM 16 SQL Reference PDF I haven't found one so far.

 

The supported functions that remove white space don't appear to remove spaces within a string, only leading and trailing spaces. Anyone know a way to solve this with SQL only?

Outcomes