Get only number from a string after a particular letters or symbol

[MK9885]

Hi,

I have a string coming in from DB as an example

John Doe address is 1234 Street ABC City NY 98756 & 45612

Here I'd just like to get numbers after NY, but not the symbol '&' just numerics

Would like to create 2 fields out of above string

1st field should have 98756

2nd field should have 45612

Tried len & keepchar, doesn't work or maybe I'm missing something.

Any help is appreciated @sunny_talwar  or anyone else..

[MK9885]

Delimitator is not fixed 
it can be ',' or '-' or just blank space

[MK9885]

Please see attached field
Need Numbers only after CPT without any delimiter
First number should be Field 1

second is Field 2 & third is Field 3 

[QFabian]

this works for me :

=subfield(replace(purgechar(trim(mid(yourfield,findoneof( vA, 'NY')+2)), '&-,'), ' ', ' '), ' ', 1)

=subfield(replace(purgechar(trim(mid(yourfield,findoneof( vA, 'NY')+2)), '&-,'), ' ', ' '), ' ', 2)

[MK9885]

Doesn't work for me...@Fabian.

Did you see my attached file?

[QFabian]

this work for me :

=subfield(replace(purgechar(trim(mid(vA,findoneof( yourfield, 'NY')+2)), '&-,'), ' ', ' '), ' ', 1)

=subfield(replace(purgechar(trim(mid(vA,findoneof( yourfield, 'NY')+2)), '&-,'), ' ', ' '), ' ', 2)

[Kushal_Chawda]

@MK9885  try below

LOAD *,
trim(subfield(SubField(Text,'NY',-1),'&',1)) as Field1,
trim(subfield(SubField(Text,'NY',-1),'&',2)) as Field2
Inline [
Text
John Doe address is 1234 Street ABC City NY 98756 & 45612 ];

[MK9885]

@Kushal_Chawda 
Please see attached data file on top of this page
test field can be CPT or SCHS
delimiter can be , & - or just blank space

Thanks. 

[rwunderlich]

Ok. So what is constant is "NY" (I take it those that don't have NY you don't care about).  After NY is 0 to 2 numbers. Those numbers  may be separated by "-", ",", "&" or space, plus any number of spaces.  Here's a script that may do the trick (another alternative is to use a regular expression if you or someone else can code the regex to do the parsing).  

SpaceMap:
Mapping LOAD Repeat(' ', RecNo()+1), ' ' AutoGenerate 20;

DelimMap:
Mapping LOAD delim, ' '
Inline [
delim
&
","
-
]
;

LOAD
  *,
  SubField(zips, ' ', 1) as Zip1,
  SubField(zips, ' ', 2) as Zip2,
;
LOAD
  source,
  MapSubString('SpaceMap'
    ,MapSubString('DelimMap'
      ,Trim(SubField(source, 'NY', 2))
  )) as zips
INLINE [
source
"John Doe address is 1234 Street ABC City NY 98756 & 45612"
"John Doe address is 1234 Street ABC City NY 98756&45612"
"John Doe address is 1234 Street ABC City NY 98756,45612"
"John Doe address is 1234 Street ABC City NY 98756, 45612"
"John Doe address is 1234 Street ABC City NY 98756 - 45612"
"John Doe address is 1234 Street ABC City NY 98756-45612"
"John Doe address is 1234 Street ABC City NY 98756  -  45612"
"John Doe address is 1234 Street ABC City NY 98756 45612"
"John Doe address is 1234 Street ABC City NY "
"Jane Doe address is 1234 Street ABC City WI "
"Jane Doe address is 1234 Street ABC City WI 12345 54321"
"John Doe address is 1234 Street ABC City NY 98756"
] ;

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[MK9885]

@rwunderlich 
Thanks for the reply..
tried your script, didn't work..
I have attached the field where I just need numbers after CPT (which are 5 digits)
some times I have two 5 digits numbers. 
1st 5 digit Number should be field 1 & 2nd 5 digit number should be field 2.

Example:
CT CTA Aorta/Bilat Iliofem Runoff (C-/C+) CPT 71275, 75635

Field1
71275
Field2
75635
Some times I have only Field1 or I have 3 fields
I hope that makes sense? 

[edwin]

follow on to Rob's idea, first identify the state.  then get the right of the state then parse that:

subfield(text, state,-1) is the string that has the two zip codes;  then parse that (you need to trim it first)

load source ,

replace(replace(replace(upper(source),'&',' '),',',' '),'-',' ') as cleaneduptxt, //removed special characters seprating the two zip codes - change to space

replace(replace(replace(
subfield(rtrim(KeepChar(upper(source),'ABCDEFGHIJKLMANOPQRSTUVWXYZ ')),' ',-1)
,'&',' '),',',' '),'-',' ') as State, // this is the state or last string before the two zip codes

//this selects the first number after the last alpahbetic string (can be NY, VA, CA, CPT, etc..
subfield(ltrim(rtrim(SubField(

//this is the cleaned up text
replace(replace(replace(upper(source),'&',' '),',',' '),'-',' '),

//this is the state - using it as the delimeter
replace(replace(replace(
subfield(rtrim(KeepChar(upper(source),'ABCDEFGHIJKLMANOPQRSTUVWXYZ ')),' ',-1)
,'&',' '),',',' '),'-',' ')

,-1))),' ',1)as Zip1,

subfield(ltrim(rtrim(SubField(

replace(replace(replace(upper(source),'&',' '),',',' '),'-',' '),

replace(replace(replace(
subfield(rtrim(KeepChar(upper(source),'ABCDEFGHIJKLMANOPQRSTUVWXYZ ')),' ',-1)
,'&',' '),',',' '),'-',' ')

,-1))),' ',1)as Zip2

INLINE [
source
"John Doe address is 1234 Street ABC City NY 98756 & 45612"
"John Doe address is 1234 Street ABC City NY 98756&45612"
"John Doe address is 1234 Street ABC City NY 98756,45612"
"John Doe address is 1234 Street ABC City NY 98756, 45612"
"John Doe address is 1234 Street ABC City NY 98756 - 45612"
"John Doe address is 1234 Street ABC City NY 98756-45612"
"John Doe address is 1234 Street ABC City NY 98756 - 45612"
"John Doe address is 1234 Street ABC City NY 98756 45612"
"John Doe address is 1234 Street ABC City NY "
"Jane Doe address is 1234 Street ABC City WI "
"Jane Doe address is 1234 Street ABC City WI 12345 54321"
"John Doe address is 1234 Street ABC City NY 98756"
"CT CTA Aorta/Bilat Iliofem Runoff (C-/C+) CPT 71275, 75635"
] ;

this solution assumes that the State appears only once and it positioned right before the zip codes