finding values from a string

[JamesFlowers]

In the string below I have a number "242"


InfoService.QuickReportStart(SalNumberToStrX(242,0)||'@CONSIGNMENTID='||hWndForm.frmConsignment.ecmbnConsignmentId.i_sMyValue)


I am trying to get this number on its own to link it to another field.

I cant substring as there might be a varied length of string before the number I need to use as the process uses different code after the "InfoService." sometimes.

Any help would be appreciated.

Regards

James Flowers

 

[lewisp]

If you could guarantee that there will always be 2 open brackets before the number you could use INSTR to find the location of the second bracket, then SUBSTR the resultant string. eg

[tt]SELECT substr(<your str>,
Instr(<your str>,'(',1,2) + 1,
Instr((<your str>,',') - 1 -
Instr((<your str>,'(',1,2)))
FROM dual;[/tt]

works for the string in your example.

 

[sem]

If you have a character that is not used in such strings (say ~), you may find a position of the first digit by

pos := instr(translate(str,'0123456789',rpad('~',10,'~')),'~');

Then you may cut off inital non-numerics:

nstr := substr(str, pos);


Then you may calculate the length of the sequence of initial digits:

lngth := length(nstr)-length(ltrim(nstr,'~'));

And after all you may calculate the number:

digs := to_number(substr(nstr, lngth));

The whole formula is too complex to explain its origin


Regards, Dima

http://seminihin.narod.ru