Extract a six digit number from a string that also contains other numbers

[canoebi99]

Hi All,

I need some assistance from some experts out there!

I have a string field that contains a job number and I need to extract the job number.

Unfortunately there are other numbers in the string which complicates things.

Example strings that I am working with are below:

Microsoft Word - 316680_15196160415_CQP Stage 3_bes               - I need to extract 316680 from this string

12275 151028 Stage 1 2 Post Construction Audit                              - I need to extract 151028 from this string

So basically any 6 digit string needs to be extracted.

Does anyone have any ideas as to how I can do this (in the script)?

Many thanks

Ben

[Mark_Little]

Hi,

Would they always be like the above?

As you could replace space with _ so they are formatted the same and then use subfield

Something like SUBFIELD(REPLACE(Field,' ','_'),'_',2) as Extracted?

Mark

[canoebi99]

Thanks mark, but unfortunately no.

There are all kinds of combinations of text and numbers.

I need someway of only extracting those numbers found in the string that are exactly 6 numbers long regardless of what is either side of them. (space, underscore etc)

I think I may be getting close with a regex function that I have found but I am still playing with it.

I will post back if I get it to work.

[Anonymous]

Here concern is what, you dont have fix format of string.

Some where you are having '_', ' ' and may be some other special character.

Could you post some sample data?

[Kushal_Chawda]

try something like this,

Data:

LOAD *, SubField(Replace(Field,' ','_'),'_') as Output Inline [

Field

316680_15196160415_CQP Stage 3_bes              

12275 151028 Stage 1 2 Post Construction Audit ];

Inner Join

LOAD Distinct Data,

Output

Resident Data

where len(Field)=6;

[Kushal_Chawda]

what if there is two numbers with length six? fro eg. 12 12345 abc 456788

[pljsoftware]

Hi Ben,

this is the code

t0:
LOAD
  Field
  ,SubField(value, '_') as value
;
LOAD
  *
  ,SubField(Field, ' ') as value
;
LOAD * Inline [
Field
Microsoft Word - 316680_15196160415_CQP Stage 3_bes
12275 151028 Stage 1 2 Post Construction Audit
12275 000028 Stage 1 2 Post Construction Audit
];

t1:
LOAD
  Field
  ,value as OnlyNum
Resident t0
Where IsNum(KeepChar(value, '0123456789')) and Len(KeepChar(value, '0123456789')) = 6;

I have added a you row with 000028 to test the result of my where condition and is all ok.

Best Regards

Luca Jonathan Panetta

[Gysbert_Wassenaar]

This will do the trick;

LOAD *, KeepChar(Mid(SubString,2,6),'0123456789') as Number
WHERE
  not IsNum(Left(SubString,1))
  and not IsNum(Right(SubString,1))
  and KeepChar(Mid(SubString,2,6),'0123456789') = Mid(SubString,2,6);
LOAD *, Text(Mid(TestString,IterNo(),8)) as SubString, IterNo() as ID
While IterNo() <= Length-8;
LOAD *,'@' & String & '@' as TestString, Len(String)+2 as Length INLINE [
String
Microsoft Word - 316680_15196160415_CQP Stage 3_bes
12275 151028 Stage 1 2 Post Construction Audit 
];