Extract 1st occurence of number from part of the string

[#1]

Hi All,

I have string from which I want to extract the 1st occurence of number. Let me explain with example

for example I have string  AB-BC-A250B-B12C-AD .In this  string you can see the 1st occurence of number is in part A250B i.e 250

Below are some more examples with output

                                       Output

AB-BC-A250B-B12C-AD     250

AD-A2G0B-B12C-AD          20

BG-12-150A-BD                 12

GB-A120-B130-CG            120

[Kushal_Chawda]

is there any possibility of any other string separator instead of '-'?

[#3]

No, string will be separated by '-' only

[daveamz]

Hi,

Check the example attached.

It may run slow on large and very large data sets.

Regards,

David

[Kushal_Chawda]

Data:

LOAD *,RowNo() as Row, KeepChar(subfield(Field,'-'),'0123456789') as Field1 Inline [

Field

AB-BC-A250B-B12C-AD

AD-A2G0B-B12C-AD

BG-12-150A-BD

GB-A120-B130-CG ];

Left Join

LOAD Field,

     min(Row) as Row,

     1 as MinRow

Resident Data

where len(trim(Field1))>0

Group by Field;

Final:

NoConcatenate

LOAD Field,

     Field1 as Output

Resident Data

where MinRow=1;

DROP Table Data;

[alexandros17]

This is what you need:

AAA:
LOAD * Inline [
Campo, Out
AB-BC-A250B-B12C-AD, 250
AD-A2G0B-B12C-AD, 20
BG-12-150A-BD, 12
GB-A120-B130-CG, 120
];

BBB: NoConcatenate
LOAD Campo, Mid(Str2, 1, Len2-1) as String;
LOAD
Campo,
KeepChar(Campo, '1234567890-') as NewCampo,
FindOneOf(KeepChar(Campo, '1234567890-'),'1234567890') as Pos1,
Mid(KeepChar(Campo, '1234567890-'),FindOneOf(KeepChar(Campo, '1234567890-'),'1234567890')) as Str2,
Index(Mid(KeepChar(Campo, '1234567890-'),FindOneOf(KeepChar(Campo, '1234567890-'),'1234567890')),'-') as Len2
Resident AAA;
DROP Table AAA;

[Clever_Anjos · Accepted solution]

Please check if fits your needs

T:
MAPPING LOAD
  chr(ord('0')+RecNo()-1) AS  c,
  '*'
AutoGenerate ORD('9') - ORD('0');

Data:
LOAD *,
  KeepChar(SubField(Mid(Field,Index(t,'*')),'-',1),'0123456789') as finalfield;
LOAD
  *,
  MapSubString('T',Field) as t
Inline [
Field
AB-BC-A250B-B12C-AD
AD-A2G0B-B12C-AD
BG-12-150A-BD
GB-A120-B130-CG ];

drop Field t;

[#8]

excellent clever

[#9]

cleveranjos

only one modification require . Your mapping table only maps numbers from 0 to 8.. there is possibility of number starting from 9. So I did below changes.. do let me know if did it correct

LOAD
chr(ord('0')+RecNo()-1) AS c,
'*'
AutoGenerate ORD('9') - ORD('0')+1;

[Clever_Anjos]

Perfect, I was wrong