IsNumericList() Function

Whipped up this UDF while working on a project. It checks to see if the list is valid to use in a TSQL WHERE IN Clause. I wanted to check for a list of valid positive integers (including 0), ignoring spaces.

<cffunction name="isNumericList" returntype="boolean" hint="Check for either single positive number or a set of positive numbers. Spaces ignored." >
 
  <!--- Useful when inserting into an "IN" TSQL list in the WHERE clause.  --->
 
  <cfargument name="list" required="true">
 
  <cfargument name="delimiter" required="false" default=",">   
 
  <cfset var isNumericList = false />
 
  <cfif REFind( "^(\d+)$|^(([\d\s]+#Arguments.delimiter#)+\s*\d+)$", Trim(Arguments.list) ) >
 
    <cfreturn true >
 
  </cfif>
 
  <cfreturn isNumericList >  
 
</cffunction>

Now let’s test it!

<!--- Dummy Data --->
<cfset dataList = ArrayNew(1) />
<cfset dataList[1] = "456c" />
<cfset dataList[2] = "456" />
<cfset dataList[3] = "45c," />
<cfset dataList[4] = "5,,," />
<cfset dataList[5] = "565,651,34,643232,45" />
<cfset dataList[6] = "454,c,45,5454,32" />
<cfset dataList[7] = "121.45,43,565,1,1,2" />
<cfset dataList[8] = "43,54,65,1," />
<cfset dataList[9] = "67,54,73,436," />
<cfset dataList[10] = ",6565,656,77,32,3" />
<cfset dataList[11] = "" />
<cfset dataList[12] = ",43656" />
<cfset dataList[13] = "4365,  55,31,24,   5,   5  ,1,      34" />

Now let’s use it!

<!--- Use it! --->
<cfoutput>
   <cfloop array="#dataList#" index="i">  
      [#i#] => #isNumericList(i)#<br />
   </cfloop>
</cfoutput>

Results!

[456c] => false
 
[456] => true
 
[45c,] => false
 
[5,,,] => false
 
[565,651,34,643232,45] => true
 
[454,c,45,5454,32] => false
 
[121.45,43,565,1,1,2] => false
 
[43,54,65,1,] => false
 
[67,54,73,436,] => false
 
[,6565,656,77,32,3] => false
 
[] => false
 
[,43656] => false
 
[4365,  55,31,24,   5,   5  ,1,      34] => true

Leave a Reply