topic Re: Extract number from string in App Development

Extract number from string

venkatbza — Tue, 30 Nov 2021 18:17:33 GMT

Hi All,

I have a comments column and in that I have service number and along with some comments and dates. In that I have to strip only service number. service numbers are mostly having 8 digits but few cases service numbers are 7 and 9 digits.

sample data posted below, help me out how I can extract only numbers(highlighted red color) from a given column.

Comments

DLL DISC / 02.09.2021/ 30364307/abc

LEASE DISC / 11.23.2020/10117134/def

LEASE DISC / 11.23.2020/10127588/Integr

LEASE DISC / 10101466, 10143520 / ADFC

LEASE DISC / 10111445 / 80800005

LEASE DISC / 10131743, 10149069 / not

LEASE DISC / 10133124/ 10146144 QUALITY

LEASE DISC / 10145033 / 10118692 / Comm

LEASE DISC / 10146155/10135854 / Avffd

LEASE DISC / 10147220/10104449 /kjkdj

LEASE DISC / 10147431, 10147394 / hdhdh

LEASE DISC / 30335157 / 1518833 hhdyyy

LEASE DISC /10104449, 10147220 RYETBDJ

LEASE DISC/10105402-10144528/TRHJDSNJ

LEASE DISC/10124511-10147275/HTHRJHJ

LEASE DISC/10143314-10144617/hdjhjdhk

PMC DISC / 10141863, 10147184 / kokoo

PMC DISC 91000010 /10141863/hhdj ddd

PMC DISC 91000010 /10145554/jjjdjd'sss

PMC/ADV MED HOMECARE/10131042 10144358

PMC/DYNAMIC HEALTHCARE/10146887 10101760

WIRE FEE / 08-26-2020/10106466/MEDICAL

WIRE FEE/03.26.2021/10141187/TG OXYGEN

10100465 Lease Disc

10101498 Lease Disc

10101760 Lease Disc

10123751 / hddyytr nnn

10125787 Lease Disc

10126312 /reed nhhd lllj

10127600 Lease Disc

10140316 Lease Disc

10146887 Lease Disc

ACH / 10101498 / hhdjh jkeebeel

ACH 91000016/AUGUST PREFUND/REBATE

WIRE FEE/03.26.2021/955843261/TG OXYGEN

LEASE DISC / 1014368 / bvcded Consulting

Thanks

Re: Extract number from string

GaryGiles — Mon, 07 Jun 2021 21:05:09 GMT

I would approach this by first normalizing the character that is separating the values and then using the subfield() function to extract the parts. This does mean that you have to anticipate the characters that are used to separate the fields. Something like this:

NoConcatenate

Comments:

Load Load Key, replace(replace(replace(replace(Comments,'#',','),' ',','),'/',','),'-',',') as Comments

Resident [table with comments];

CommentParts:

Load subfield(Comments,',') as CommentPart
Resident Comments;

NoConcatenate

ServiceNumber:

Load CommentPart as ServiceNumber

Resident CommentParts
where IsNum(CommentPart) and len(CommentPart) >= 7 and len(CommentPart) <=9;

drop table CommentParts;

If you need to have the Service number in the same table as the original table, you can in the key field so that you can join it back to the original table.

Re: Extract number from string

venkatbza — Wed, 09 Jun 2021 08:14:25 GMT

Hi GaryGiles,

Thanks for your suggestion , it works for me.