TryCF Gist

trycf-gist-1541165972774-e98d47e8-ae94-ccd5-6cdd-2c76ce06095a.cfm

<cfscript>
all_employees = QueryNew("userdefined,hello", "varchar,varchar",
    [
      ["test","pure text"],
      ["2","number as varchar"],
      ["03","leading zero"],
      [" 4 ","leading and trailing spaces"],
      ["5        ","extra trailing spaces"],
      ["        6","extra leading spaces"],
      ["165e73","scientific notation"],
      ["1.5","decimal"],
      ["1,5","comma-delimited (or non-US decimal)"],
      ["1.0","valid decimal"],
      ["1.","invalid decimal"],
      ["1,000","number with comma"]
      
    ]
)

//writedump(all_employees) ;

retval = [] ;

for (r in all_employees) {
    retval.append(
        {
            "1 - RowInput"   : r.userdefined.replace(" ","*","all") , // Replace space with * for output visibility.
            "2 - IsNumeric?" : ISNUMERIC(r.userdefined) ,
            "3 - FirstOutput": ( ISNUMERIC(r.userDefined) ? right("00000000"&r.userdefined,8) : r.userDefined ) ,
            "4 - ValOutput"  : ( ISNUMERIC(r.userDefined) ? right("00000000"&val(r.userdefined),8) : r.userDefined ) ,
            "5 - TrimOutput"  : ( ISNUMERIC(r.userDefined) ? right("00000000"&ltrim(rtrim((r.userdefined))),8) : r.userDefined )
        } 
    ) ;
}

writeDump(retval) ;
</cfscript>