Create a new double-field based on the existing string-filed-CodePudding

There is an SQL query forming a table. It is necessary to form another double-number field based on one of the string fields in the table.

Values of the original string field (BRLOADSTA0):

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

Values of the resulting double-field (BRLOADSTA9):

The request which I've create:

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

Full snippet:

  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)));

Thanks in advance even for trying to help :)

CodePudding user response：

Could you tell us which datatype on BRLOADSTA0? I tested using those data provided by you. Please see the below sample.

Demo

try:

SELECT CAST(replace(split_part(BRLOADSTA0,E'\-',3),E\',',E'\.') AS NUMERIC) BRLOADSTA9

SELECT to_number(replace(split_part(BRLOADSTA0,E'\-',3),E'\,',E'\.'),'99999.99') BRLOADSTA9

standard_conforming_strings (boolean)

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off). Applications can check this parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported. Escape string syntax (Section 4.1.2.2) should be used if an application desires backslashes to be treated as escape characters.

Documentation

try again:


'to_number(double,replace(split_part(BRLOADSTA0,'   chr(39)   chr(45)   chr(39)   ',3),'   chr(39)   chr(44)   chr(39)   ','   chr(39)   chr(46)   chr(39)   '),'   chr(39)   '99999.99'   chr(39)   ') as BRLOADSTA9, '