While jonathan200's answer is correct based on your example data, it depends on the data in char_field being previously validated to be in the correct format as if the 'A' element does not have a numeric portion, B's number would be returned as A's number thus giving incorrect results. So, depending on how this data was previously entered and validated you may or not have an issue. However, if this data came from a user input on a form or from unscrubbed input then I would suggest you tighten up the regex to further allow only what you expect. But then you need to decide how to detect and handle that.
Consider this query using a WITH statement (known as a CTE or Common Table Expression) to set up some test data. Always use test data to set up unexpected data formats when testing. Instead of just looking for numbers, the regex for a_units matches a string starting with an 'A' followed by a group of 1 to 7 digits followed by a group consisting of a comma and returns the first group (the digits). If you don't care about the number of digits after the 'A', then replace '{1,7}' with a '+' which means 1 or more. NOTE that regexp_substr returns NULL if the match is not found.
with tbl(id, char_field) as (
select 1, 'A5,P0' from dual union all
select 2, 'A15,P1' from dual union all
select 3, 'A6,P12' from dual union all
select 4, 'A1234567,P1234567' from dual union all
select 5, 'A1234567890,P0987654321' from dual union all
select 6, NULL from dual union all
select 7, 'A,P' from dual union all
select 8, 'A,P0' from dual union all
select 9, 'P12, A877' from dual
)
SELECT id, char_field,
REGEXP_SUBSTR(char_field, 'A(\d{1,7})(,)', 1, 1, NULL, 1) AS a_units,
REGEXP_SUBSTR(char_field, 'P(\d{1,7})($)', 1, 1, NULL, 1) AS p_units
FROM tbl
order by id;
ID CHAR_FIELD A_UNITS P_UNITS
-- ----------------------- ----------------------- -----------------------
1 A5,P0 5 0
2 A15,P1 15 1
3 A6,P12 6 12
4 A1234567,P1234567 1234567 1234567
5 A1234567890,P0987654321
6
7 A,P
8 A,P0 0
9 P12, A877
9 rows selected.
If you need to detect if a match is not found, you could use regexp_replace, which returns the original string if the match is not found, then compare it to char_field and if they are equal and not null, the match was not found. With regex_replace, the '\1' means replace the match with the first grouping.
with tbl(id, char_field) as (
select 1, 'A5,P0' from dual union all
select 2, 'A15,P1' from dual union all
select 3, 'A6,P12' from dual union all
select 4, 'A1234567,P1234567' from dual union all
select 5, 'A1234567890,P0987654321' from dual union all
select 6, NULL from dual union all
select 7, 'A,P' from dual union all
select 8, 'A,P0' from dual union all
select 9, 'P12, A877' from dual
)
SELECT id, char_field,
REGEXP_REPLACE(char_field, 'A(\d{1,7}),.*', '\1') AS a_units,
REGEXP_REPLACE(char_field, '.*P(\d{1,7})$', '\1') AS p_units,
case
when REGEXP_REPLACE(char_field, 'A(\d{1,7}),.*', '\1') is not null AND char_field = REGEXP_REPLACE(char_field, 'A(\d{1,7}),.*', '\1') then
'A NO MATCH'
when REGEXP_REPLACE(char_field, 'A(\d{1,7}),.*', '\1') is not null AND char_field != REGEXP_REPLACE(char_field, 'A(\d{1,7}),.*', '\1') then
'A Matched'
else
'UNKNOWN'
end as match
FROM tbl
order by id;
ID CHAR_FIELD A_UNITS P_UNITS MATCH
-- ----------------------- ----------------------- ----------------------- ----------
1 A5,P0 5 0 A Matched
2 A15,P1 15 1 A Matched
3 A6,P12 6 12 A Matched
4 A1234567,P1234567 1234567 1234567 A Matched
5 A1234567890,P0987654321 A1234567890,P0987654321 A1234567890,P0987654321 A NO MATCH
6 UNKNOWN
7 A,P A,P A,P A NO MATCH
8 A,P0 A,P0 0 A NO MATCH
9 P12, A877 P12, A877 P12, A877 A NO MATCH
9 rows selected.
So anyway, my purpose with all this is to highlight the fact that you need to include even unexpected test conditions, decide how you need to detect and handle exceptions (or even if you have to at all), and know that regexp_substr and regexp_replace return different values if the match is not found so perhaps one is more appropriate to use than the other depending on your circumstances. Stuff to keep in the back of your mind when developing your queries.
