Extend FM SQL to include REPLACE() from T SQL

Idea created by coherentkris on Sep 29, 2017
    Active
    Score8

    I completely understand the likelihood of FM implementing something NOT from SQL 92 is slim but here goes anyway.

     

    IF we had replace we could do "fuzzy" matching without the need to create extra calculation fields in a table.

    i.e this data:

    table phones
    pkphonenumber
    112 123
    21 2123
    3121 23
    41212 3
    532134
    6321 33

    and ExecuteSQL ("SELECT phonenumber FROM phones WHERE REPLACE( phonenumber , ' ' , '' ) =?" ; "" ; "" ; 12123)

    Would return 1, 2, 3, 4

     

     

    REPLACE (Transact-SQL)

     

    Syntax

    REPLACE ( string_expression, string_pattern , string_replacement )

    Arguments

    string_expression

    Is the string expression to be searched. string_expression can be of a text data type.

    string_pattern

    Is the substring to be found. string_pattern can be of text data type.

    string_replacement

    Is the replacement string. string_replacement can be of text or numeric data type.

     

    Return Types

    Returns text data type.

    Returns NULL if any one of the arguments is NULL.

    Returns ? if syntax error.