基于字符串字段BRLOADSTA0创建Double类型字段BRLOADSTA9的SQL咨询

需求说明 #

需要基于表中字符串字段BRLOADSTA0创建Double类型字段BRLOADSTA9，提取原字段中的数字部分（将逗号分隔符替换为点）转换为数值类型。

原字段BRLOADSTA0示例值 #

АК-11 НК-82,7
АК-11 НК-82,8
АК-11 НК-82,9
АК-11 НК-82,10
АК-11 НК-82,11

目标字段BRLOADSTA9预期值 #

82.7
82.8
82.9
82.10
82.11

你编写的SQL片段 #

'to_number(double,replace(split_part(BRLOADSTA0,''-'',3),'','',''.''),''99999.99'') as BRLOADSTA9, '

完整代码片段 #

Table_mo0000: TTable_Dbf53 = (lname : mo0000;
                                sql : 'SELECT GID,IDBR,BRNAME,IDOWNORG,RDADDNUM,RDADDNUM2,BRKM,RDLANE,BRLEN,BRRIWIDTH,BRVDIM,BRUDIM,BRRWAYL,BRRWAYR,BRFULLOK,CLSBRTYP,NMBRTYP,CLSBRMAT,NMBRMAT,BRLIGHT,BRSCHEM,BRDESLOAD,BRLOADSTA0,BRLOADCRA0,BRLOADSTA1,BRLOADCRA1,LIMMASS,'+
                                'LIMMASSCOL,LIMLOAD,LIMLOADCOL,LIMHEIGHT0,LIMWIDTH,LIMDIST,LIMSPEED,LIMHEIGHT1,BROVER,DATETEST,CLSTESTORG,NMTESTORG,DATEVISIT,CLSVISORG,NMVISORG,GRDCUR,GRDFACT,NMBRREG,BLDYEAR,RECYEAR,CAPYEAR,CURYEAR,OKRYEAR,BRREGADV,BROFFADV,BRDMHEIGHT,'+
                                'to_number(double,replace(split_part(BRLOADSTA0,''-'',3),'','',''.''),''99999.99'') as BRLOADSTA9, '+ 
                                'ST_AsText(ST_Transform(geom_tilemill,28405))geom from bdc.most';
                                sql_graph: '';
                                auto_num : 0;c_fields : 54; t_geom : qmGeometryTypePoint; measure : false; km_gps : false;
                                f_fields : ((fName : 'ID';         fLength : 0;   fPrecision : 10; fScale : 0; fType : qmFieldTypeLong),
                                            (fName : 'IDBR';       fLength : 0;   fPrecision : 10; fScale : 0; fType : qmFieldTypeLong),
                                            <...>
                                            (fName : 'BROFFADV';   fLength : 250; fPrecision : 0;  fScale : 0; fType : qmFieldTypeString),
                                            (fName : 'BRDMHEIGHT'; fLength : 0;   fPrecision : 5;  fScale : 2; fType : qmFieldTypeDouble),
                                            (fName : 'BRLOADSTA9'; fLength : 0;   fPrecision : 5;  fScale : 2; fType : qmFieldTypeDouble)));

验证结果与优化建议 #

问题点 #

你编写的SQL片段存在语法错误：结合ST_Transform可判断使用的是PostgreSQL数据库，该数据库中to_number函数的正确语法为to_number(text, text)，你额外添加的double参数无效，会导致查询报错。

修正后的SQL片段 #

'to_number(replace(split_part(BRLOADSTA0,''-'',3),'','',''.''),''99999.99'') as BRLOADSTA9, '

优化建议 #

考虑到字段可能存在格式不符合预期的情况（比如BRLOADSTA0为空、分割后无第三部分、包含非数字内容等），建议使用try_to_number函数（PostgreSQL 12及以上版本支持），它在转换失败时会返回NULL而非直接抛出错误，避免整个查询中断：

'try_to_number(replace(split_part(BRLOADSTA0,''-'',3),'','',''.''),''99999.99'') as BRLOADSTA9, '

另外，split_part(BRLOADSTA0, '-', 3)的逻辑符合需求：原字段按-分割后第三部分确实是目标数字段（如示例中的82,7），无需调整。

内容的提问来源于stack exchange，提问作者Richard Leshik