I have a dataset which may store an account number in several different variations. It may contain hyphens or spaces as segment separators, or it may be fully concatenated. My desired output is the first three and last 5 alphanumeric characters. I'm having problems with joining the two segments "FIRST_THREE_AND_LAST_FIVE:
with testdata as (select '1-23-456-78-90-ABCDE' txt from dual union all
select '1 23 456 78 90 ABCDE' txt from dual union all
select '1234567890ABCDE' txt from dual union all
select '123ABCDE' txt from dual union all
select '12DE' txt from dual)
select TXT
,regexp_replace(txt, '[^[[:alnum:]]]*',null) NO_HYPHENS_OR_SPACES
,regexp_substr(regexp_replace(txt, '[^[[:alnum:]]]*',null), '([[:alnum:]]){3}',1,1) FIRST_THREE
,regexp_substr(txt, '([[:alnum:]]){5}$',1,1) LAST_FIVE
,regexp_substr(regexp_replace(txt, '[^[[:alnum:]]]*',null), '([[:alnum:]]){3}',1,1) FIRST_THREE_AND_LAST_FIVE
from testdata;
My desired output would be:
FIRST_THREE_AND_LAST_FIVE
-------------------------
123ABCDE
123ABCDE
123ABCDE
123ABCDE
(null)