字符串拆分后再计算

【问题】
I have a MySQL column that is defined as text. The column, if not null, always contains a list floats separated by a newline character.

I have been tasked with making the total amount of those floats searchable with min and max constraints.

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

Is this possible?

有人给出答案:

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$$

【回答】

用 SQL 做拆串处理很麻烦,用 SPL 只要两行:

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

脚本写好后,在其他应用程序中,就可以利用集算器 JDBC 调用该数据集了。Java 如何调用 SPL 脚本

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值