Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

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

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..

 

Labels (1)
21 Replies
Taoufiq_Zarra

@MK9885  you can try this :

Data:

load Field,keepchar(subfield(Sub1,'&',1),'0123456789') as FirstField,keepchar(subfield(Sub1,'&',2),'0123456789')  as SecondField;
load *,mid(Field,index(Field,'NY')) as Sub1 inline [
Field
John Doe address is 1234 Street ABC City NY 98756 & 45612
];

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Chanty4u
MVP
MVP

Hi ,

can you try this?

test:
load *
,
subfield(string, ' ', -3) as Las2nd,
subfield(string, ' ', -1) as LastWord ;
LOAD * INLINE [
string
John Doe address is 1234 Street ABC City NY 98756 & 45612
];

EXIT SCRIPT;

subfield.PNG

QFabian
Specialist III
Specialist III

Hi, are the data be the same numbers of words in each record?

if yes :

SubField(yourtext, ' ', 6)

SubField(yourtext, ' ', 😎

if not :

try @Taoufiq_Zarra  excelent solution, or another combination because of the posibilities of changing part of the text, for example, 'NY' text could be another city or state?.

QFabian
MK9885
Master II
Master II
Author

Hi Chanty,

I tried your expression, it works for few of the values but not all.

for some I'm even getting alphabets as values

Correction****
John Doe address is 1234 Street ABC City NY 98756 , 45612

Instead of '&' I have , in between those numbers

MK9885
Master II
Master II
Author

Hi Taoufiq,

Your expression is giving me only numerics, which is what I want but it giving me random numbers where I do not have numbers after NY or even do not have NY

Ex: John Doe address is 1234 Street ABC City NY 

this string is giving me 1234

But I only need anything only after NY & if it is a number only.

I'd just need anything after NY & numbers between symbol

Thanks.

eliko_il
Contributor
Contributor

Hi,

Please try this:

Trim(SubField(Trim(SubField('John Doe address is 1234 Street ABC City NY 98756 & 45612','NY',2)),'&',1)) as Field1,

Trim(SubField(Trim(SubField('John Doe address is 1234 Street ABC City NY 98756 & 45612','NY',2)),'&',2)) as Field2

MK9885
Master II
Master II
Author

Fabian,

NY will not change. Wht's changing is 78945-45612 or it can be 78945 45612 or it can be 78945 , 45612
and some of those strings do not even have those numbers or do not have NY.
I hope it makes sense.

MK9885
Master II
Master II
Author

Eliko, thanks but don't you think hard coding will not work?

Tried, doesn't work

eliko_il
Contributor
Contributor

Hi,

I didn't mean to write the script as hard coded its just a solution to your example.

Here is the same code but generic:

Trim(SubField(Trim(SubField(STRING_FIELD_FORM_DB,STRING_DELIMETER,2)),'&',1)) as Field1,

Trim(SubField(Trim(SubField(STRING_FIELD_FORM_DB,STRING_DELIMETER,2)),'&',2)) as Field2

If its doesnt work please attach qvw example.