Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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:
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;
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?.
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
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.
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
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.
Eliko, thanks but don't you think hard coding will not work?
Tried, doesn't work
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.