@TrimPunctuation ( text )

Universal punctuation trimmer for relational filters

Be the first to rate this function Log in to vote

Alex Zueiv - Show more from this author
Tashkent
https://github.com/zueiv

Sample input:

@TrimPunctuation ( "a - 1 ¶ a — 1 ¶ a : 1" )

Sample output:

a 1
a 1
a 1

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// @TrimPunctuation ( text )
//
// Universal punctuation trimmer for relational filters
// Created 2018-08-24 by Alex Zueiv
//
// Replaces all punctuation characters with spaces, and then
// TrimAll() them to create a simplified muli-key filter.
//
// Example: @TrimPunctuation ( "a - 1 ¶ a — 1 ¶ a : 1" )
//
// Because the function uses SetPrecision() to create a list of
// substitutions, the parameter must contain no more than
// 400 punctuation characters.


Let ( [

// isolate global variables
t = $t ; p = $p ; i = $i ;

// get a string of all the punctuation characters used in the text
PNCT = Substitute (
	Filter ( Upper ( text ) ; Lower ( text ) ) ;
	[ 0 ; "" ] ; [ 1 ; "" ] ; [ 2 ; "" ] ; [ 3 ; "" ] ; [ 4 ; "" ] ;
	[ 5 ; "" ] ; [ 6 ; "" ] ; [ 7 ; "" ] ; [ 8 ; "" ] ; [ 9 ; "" ] ;
	[ " " ; "" ] ; [ ¶ ; "" ]
) ;

// create a pair of substitution parameters [;] for each punctuation character
// use character codes to avoid losing pairs when processing with the UniqueValues()
$i = 0 ;
$p = PNCT ;
SBST = Replace ( Substitute (
	10 ^ Length ( PNCT ) - 1 ; 9 ;
	"& let( $i=$i+1; \";[char(\" & code(middle($p;$i;1)) & \");char(32)]\¶\" )¶"
) ; 1 ; 1 ; "" ) ;
SBST = Evaluate ( SBST ) ;

// remove duplicate substitutions
SBST = UniqueValues ( SBST ) ;
// just comment this line in FMP 15 or earlier

// substitute all punctuation characters with spaces
$t = text ;
RSLT = If ( not IsEmpty ( SBST ) ;
	Evaluate ( "substitute($t¶" & SBST & ")" ) ;
	text
) ;

// restore isolated variables
$t = t ; $p = p ; $i = i

] ;

// remove extra spaces
TrimAll ( RSLT ; 1 ; 1 )

)

Use to clean textual multi-line key fields.

Should work well in all FMP versions.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

@TrimPunctuation ( text )

Comments

Support this website.

Support this website.