Extracting specific part of column values in Oracle SQL

Question

I want to extract a specific part of column values. The target column and its values look like

TEMP_COL
---------------
DESCOL 10MG
TEGRAL 200MG 50S
COLOSPAS 135MG 30S

The resultant column should look like

RESULT_COL
---------------
10MG
200MG
135MG

You need to be clearer about the requirements. What are the rules / algorithm? For example, it could be 1. Every 2nd work; 2. The first word starting with a number; 3. Any work with MG as the suffix... etc — BobC
– BobC, Commented Mar 8, 2017 at 5:37
i didn't think of the algorithm at the time of asking the question. but i believe the required data can be extracted by reversing the column values then find like 'MG%' until the space occurs. although I couldn't figure out how to put the algorithm in actual SQL format. — AghaKhan
– AghaKhan, Commented Mar 8, 2017 at 6:50
Is it always a number followed by MG that you want to extract? — user330315
– user330315, Commented Mar 8, 2017 at 6:53

user330315 · Accepted Answer · 2017-03-08 06:58:01Z

3

This can be done using a regular expression:

SELECT regexp_substr(TEMP_COL, '[0-9]+MG')
FROM the_table;

Note that this is case sensitive and it always returns the first match.

answered Mar 8, 2017 at 6:58

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

This also assumes that the middle term uses milligrams as the unit of measure, which may not always be true.

Tim Biegeleisen · Accepted Answer · 2017-03-08 05:37:35Z

2

I would probably approach this using REGEXP_SUBSTR() rather than base functions, because the structure of the prescription text varies from record to record.

SELECT TRIM(REGEXP_SUBSTR(TEMP_COL, '(\s)(\S*)', 1, 1))
FROM yourTable

The pattern (\s)(\S*) will match a single space followed by any number of non-space characters. This should match the second term in all cases. We use TRIM() to remove a leading space which is matched and returned.

answered Mar 8, 2017 at 5:37

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

Zohar Elkayam · Accepted Answer · 2017-03-08 08:27:45Z

0

how do you know what is the part you want to extract? how do you know where it begins and where it ends? using the white-spaces?

if so, you can use substr for cutting the data and instr for finding the white-spaces.

example:

select substr(tempcol, -- string
              instr(tempcol, ' ', 1), -- location of first white-space 
              instr(tempcol, ' ', 1, 2) - instr(tempcol, ' ', 1))  -- length until next space
from dual

another solution is using regexp_substr (but it might be harder on performance if you have a lot of rows):

SELECT REGEXP_SUBSTR (tempcol, '(\S*)(\s*)', 1, 2)
FROM dual;

edit: fixed the regular expression to include expressions that don't have space after the parsed text. sorry about that.. ;)

edited Mar 8, 2017 at 8:27

answered Mar 8, 2017 at 5:40

Zohar Elkayam

3582 silver badges5 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

Your REGEXP_SUBSTR() has a problem. Do you see what it is?

Zohar Elkayam Over a year ago

@TimBiegeleisen, yup. fixed.

Collectives™ on Stack Overflow

Extracting specific part of column values in Oracle SQL

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related