getIntsFromRangeString UDF for ColdFusion / CFML - Parses integers using min/max rules to generate sorted array of INTs and visual display summarizing string.

getIntsFromRangeString.cfm

<!--- getIntsFromRangeString 2022-9-013
getIntsFromRangeString UDF for ColdFusion / CFML
Parses integers using min/max rules to generate sorted array of INTs and visual display summarizing string.
By James Moberg / SunStar Media https://www.sunstarmedia.com/
Blog: https://dev.to/gamesover/getintsfromrangestring-udf-for-coldfusion-n74
Gist: https://gist.github.com/JamoCA/db753001d64a6fd4d46d2f591769c589
Tweet: https://twitter.com/gamesover/status/1569843303084101633
2022-09-15 Add support to parse "INT+" syntax.
2022-09-15 Add support for LT, LTE, GT and GTE syntax / Use byte temp value when generating struct of unique INTs
2022-09-15 Remove default minInt/maxInt arguments. (Assuming a default maximum of 2147483647 = extremely poor performance.)
2022-09-16 Updated to use java array functions instead of struct when collecting, uniqueifying and sorting INTs
2022-09-20 Updated to return raw SQL BETWEEN OR clauses (MSSQL only support 2,100 params). Explicit cast to INT for better JSON serialization.
--->
<cfscript>
struct function getIntsFromRangeString(
	required string numberList
	,required numeric minInt
	,required numeric maxInt
	,string colName="colName"
	) hint="I accept a string of range/list values and return a struct with array of sorted integers, series text and SQL" {
	local.response = [
		"sortedValues": []
		,"seriesString": ""
		,"sql": ""
	];
	local.integers = [];
	local.s = listtoarray(javacast("string", arguments.numberList).replaceAll("[^\d,\-\+]", ""));
	if (!arraylen(local.s)) return local.response;
	// identify start/end values to series that start/end with a dash; set to 0 if out-of-limits
	for(local.a=1; local.a lte arraylen(local.s); local.a+=1){
		local.i = local.s[local.a];
		// Support for LT, LTE, GT, GTE
		if (left(local.i,1) is "<"){
			local.num = local.i.replaceAll("[^\d]", "");
			if (left(local.i,2) neq "<=") local.num -=  1;
			local.i = "-" & local.num;
		}
		if (left(local.i,1) is ">"){
			local.num = local.i.replaceAll("[^\d]", "");
			if (left(local.i,2) neq ">=") local.num +=  1;
			local.i = local.num & "-";
		}
		if (left(local.i,1) is "-" && isvalid("integer", right(local.i, len(local.i)-1))){
			if (arguments.minInt lte val(right(local.i, len(local.i)-1))){
				local.s[local.a] = arguments.minInt & local.i;
			} else {
				local.s[local.a] = -1;
			}
		} else if (listfind("+,-", right(local.i,1)) && isvalid("integer", left(local.i, len(local.i)-1))){
			if (arguments.maxInt gte val(local.i)){
				local.s[local.a] = val(local.i) & "-" & arguments.maxInt;
			} else {
				local.s[local.a] = -1;
			}
		}
	}
	// generate distinct struct with valid integers between min/max values.
	local.byte = javacast("byte",0);
	for(local.i in local.s){
		if (isvalid("integer", local.i) && local.i gte arguments.minInt && local.i lte arguments.maxInt){
			arrayappend(local.integers, javacast("int", local.i)); // single integer
		} else if (listlen(local.i,"-") is 2 && isvalid("integer", listfirst(local.i,"-")) && isvalid("integer", listlast(local.i,"-"))) {
			local.num1 = (int(val(local.i)) lt arguments.minInt) ? arguments.minInt : int(val(local.i));
			local.num2 = (int(val(listlast(local.i,"-"))) gte arguments.maxInt) ? arguments.maxInt : int(val(listrest(local.i,'-')));
			if (local.num1 lte local.num2){
				for(local.a=local.num1; local.a lte local.num2; local.a+=1){
					arrayappend(local.integers, javacast("int", local.a));
				}
			}
		}
	}
	// Uniqueify and sort array values
	local.response.sortedValues = createobject("java", "java.util.ArrayList").init(
		createobject("java", "java.util.HashSet").init(local.integers)
	);
	arraysort(local.response.sortedValues, "numeric");

	// formatListAsSeries borrowed from https://cflib.org/udf/formatListAsSeries
	local.useLastNum = false;
	local.lastNum = "";
	for (local.currNum in local.response.sortedValues) {
		if ( len(local.lastNum) eq 0 ) {
			local.response.seriesString = local.response.seriesString & local.currNum;
			local.lastNum = local.currNum;
			local.useLastNum = false;
		} else if ( local.lastNum eq local.currNum ) {
			// same; ignore
		} else if ( local.lastNum + 1 neq local.currNum ) {
			if ( local.useLastNum ) {
				local.response.seriesString = local.response.seriesString & local.lastNum;
			}
			local.response.seriesString = local.response.seriesString & ", " & local.currNum;
			local.lastNum = local.currNum;
			local.useLastNum = false;
		} else {
			if ( !local.useLastNum ) {
				local.response.seriesString = local.response.seriesString & "-";
			}
			local.lastNum = local.currNum;
			local.useLastNum = true;
		}
	}
	if ( local.useLastNum ) {
		local.response.seriesString = local.response.seriesString & local.lastNum;
	}
	// Generate SQL from seriesString
	if (len(trim(arguments.colName)) && len(local.response.seriesString)){
		local.ranges = listtoarray(local.response.seriesString);
		local.sql = [];
		for (local.range in local.ranges){
			local.range = trim(local.range);
			if (find("-", local.range)){
				arrayappend(local.sql, "(#arguments.colName# BETWEEN #val(local.range)# AND #val(listrest(local.range,'-'))#)");
			} else {
				arrayappend(local.sql, "(#arguments.colName# = #val(local.range)#)");
			}
		}
		if (arraylen(local.sql)){
			local.response.sql = "(#arraytolist(local.sql, ' OR ')#)";
		}
	}
	return local.response;
}
</cfscript>

getIntsFromRangeString_test.cfm

<cfset tests = [
	["numberList": "0, 2021- , -1978, 1980-1984,1988, 1999, 8888, abc, 1920, 1E3", "minInt":1977, "maxInt":2022, "colName":"E.EventYear"]
	,["numberList": "2,1,6,,7,8,2, 2, 10, 1-3, 9.5", "minInt":1, "maxInt":10, "colName":"R.RacesFlown"]
	,["numberList": "0, 6, 4, 22, 100-200, 5,  😀  ", "minInt":1, "maxInt":10, "colName":""]
	,["numberList": "5+, 1, 25", "minInt":1, "maxInt":10]
	,["numberList": "<5, >8", "minInt":1, "maxInt":10, "colName":"Racers.TotalHrs"]
	,["numberList": "<=5 ,8", "minInt":1, "maxInt":10, "colName":"TotalHrs"]
	,["numberList": ">5, 1", "minInt":1, "maxInt":10, "colName":"R.TotalHrs"]
	,["numberList": ">=5, 3", "minInt":1, "maxInt":10, "colName":"[R].[TotalHrs]"]
]>

<cfoutput>
	<h3>getIntsFromRangeString(numberList, <i>minInt</i>, <i>maxInt</i>)</h3>
	<cfloop array="#tests#" index="test">
		<cfset a = getIntsFromRangeString(argumentcollection=test)>
		<b>getIntsFromRangeString("#test.numberList#", <i>#test.minInt#</i>, <i>#test.maxInt#</i>)</b><br>
		<b>SortedValues:</b> <tt>#serializeJson(a.sortedValues)#</tt><br>
		<b>SeriesString:</b> <tt>"#a.seriesString#"</tt><br>
		<b>SQL:</b> <tt><cfif len(a.sql)>"#a.sql#"<cfelse><i>empty</i></cfif></tt>
		<hr>
	</cfloop>
</cfoutput>