Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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?
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;
what if there is two numbers with length six? fro eg. 12 12345 abc 456788
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
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
];