Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Hi GaryGiles,
Thanks for your suggestion , it works for me.