【Question】

I have a MySQL column that is defined as text. The column, if not null, always contains a list of floats separated by a newline character. I have been tasked with making the total number of those floats searchable with min and max constraints.

In the WHERE clause, I would like to be able to split the column by a newline character and sum all of the resulting strings as floats. Is this possible?

 

Here’s a solution:

DELIMITER $$

CREATE function split_n_sum(str text) returns DECIMAL(36,4)

begin

declare location int;

declare result decimal(36,4);

declare tmp_str varchar(1024);

declare _delimiter varchar(128);

set _delimiter=‘\r\n’;

set result=0;

set tmp_str=ltrim(rtrim(str));

set location=INSTR(tmp_str,_delimiter);

if location=0 and length(tmp_str)>0 then

set result=cast(tmp_str as decimal(36,4));

set tmp_str='';

end if;

while location<>0 do

set result = result+cast(substring(tmp_str,1,location-length(_delimiter)) as decimal(36,4));

set tmp_str=substring(tmp_str,location+length(_delimiter), length(tmp_str));

set location=INSTR(tmp_str,_delimiter);

end while;

if length(tmp_str)>0 then

set result=result+cast(tmp_str as decimal(36,4));

end if;

return result;

end$$

 

【Answer】

It’s a hassle to split strings in SQL. But it’s a piece of cake to do it in SPL (Structured Process Language):

 

	A
1	$select data from …
2	=A1.(data.split@tp("/n").sum())
3	=A2.max()|A2.min()

You can call the SPL script from another application via esProc JDBC. See How to Call an SLP Script in Java.