Extracting Integer value from a string

[Abraham Sunil Mutyala 3]

I have a string with both integer and alphanumeric

example:-

-SET &VARIABLE = ‘customer_13426, ctdacustom_6544, customer_xz_456_hguyrd’;

and I need to extract only integer value like 13426, 6544, 456

Thanks for Help!

[Martin Yergeau]

Not the most efficient but as a quick answer

-SET &VARIABLE = 'customer_13426, ctdacustom_6544, customer_xz_456_hguyrd';-SET &NUM1 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(&VARIABLE),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z','');-SET &NUM2 = REPLACE(REPLACE(REPLACE(REPLACE(&NUM1,'_',''),'-',''),'.',''),'?','');-TYPE >-&NUM2<-

[Martin Yergeau]

Little more advanced

-SET &STRG = 'customer_13426, ctdacustom_6544, customer_xz_456_hguyrd';-SET &ALPHA = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';-SET &SPEC = '!@#$%^&*()-_=+/{}"<>?|~.';-*-*To remove the spaces at the same time-*-* But STRIP may also be used to remove spaces-*-*-SET &SPEC = '!@#$%^&*()-_=+/{}"<>?|~. '; -REPEAT STRIPALPHA FOR &I FROM 1 TO 26-SET &TOKEN = SUBSTRING(&ALPHA, &I, 1);-SET &REPL = REPLACE(UPPER(&STRG), '&TOKEN.EVAL', '');-SET &STRG = &REPL;-STRIPALPHA-*-* To remove the spaces at the same time, loop 26 time with &SPEC including the space character.-*-* But STRIP may also be used to remove spaces-*-*-REPEAT STRIPSPEC FOR &I FROM 1 TO 26-REPEAT STRIPSPEC FOR &I FROM 1 TO 25-SET &TOKEN = SUBSTRING(&SPEC, &I, 1);-SET &REPL = REPLACE(&STRG, '&TOKEN.EVAL', '');-SET &STRG = &REPL;-STRIPSPEC-TYPE NEW STRING: &STRG

[David Beagan]

The PATTERNS function seems tailor-made for something like this:

-SET &VARIABLE = 'Customer_13426, ctdacustom_6544, customer_xz_456_hguyrd'; -SET &PATTERN = PATTERNS(&VARIABLE);-SET &PATTERN = REPLACE(&PATTERN, 'A','$');-SET &PATTERN = REPLACE(&PATTERN, 'a','$');-SET &PATTERN = REPLACE(&PATTERN, '_','$');-SET &PATTERN = REPLACE(&PATTERN, ' ','9');-SET &PATTERN = REPLACE(&PATTERN, ',','9');-SET &VARIABLE = EDIT(&VARIABLE, &PATTERN); -? &VAR

Output looks like this:

 

[David Beagan]

You don't say what release you are on so the PATTERNS function may not be available to you. There is the older PATTERN function which you could use.

If you are on a later release, I found something even better.

-SET &VARIABLE = REGEXP_REPLACE(&VARIABLE, '[^0-9, ]', '');

[David Briars]

We are new to 8207, coming from 8105M.

So, I love hearing about the newer features/functions, like Regular Expression support.

Many thanks all.

[Abraham Sunil Mutyala 3]

Thank you so much David!

This logic helped.