CODEWITHSUNDEEP
sqloracle-database
MateoK
MateoK

Reputation: 1

How to return a substring after a colon and before a whitespace in Oracle SQL

I need to get a substring from a table column that is after a colon and before a whitespace. The length of the substring can vary, but the length of the data before the colon and after the whitespace is constant.

So the data in my table column named "Subject" consists of 5 words, immediately followed by a colon, immediately followed by the substring I need (which can vary in length), followed by a whitespace and a date. The substring I need is a course name. Examples:

Payment Due for Upcoming Course:FIN/370T 11/26/2019

Payment Due for Upcoming Course:BUS/475 11/26/2019

Payment Due for Upcoming Course:ADMIN9/475TG 11/26/2019

I have tried using REGEXP function with REGEXP_SUBSTR(COLUMN_NAME,'[^:]+$') to get everything after the colon, and REGEXP_SUBSTR(COLUMN_NAME, '[^ ]+' , 1 , 5 ) to get data before the last whitespace, but I need to combine them.

I have tried the following: 

  select 
       REGEXP_SUBSTR(SUBJECT,'[^:]+$')  COURSE_ID

  from TABLE


  Result:
  FIN/370T 11/26/2019

and this:

  select 
       REGEXP_SUBSTR (SUBJECT, '[^ ]+' , 1 , 5 ) COURSE_ID2          

  from TABLE

  Result: 
  Course:FIN/370T

I need the output to return FIN/370T

Upvotes: 0

Views: 1851

Answers (3)

Thomas Strub
Thomas Strub

Reputation: 1285

In short use:

select regexp_replace(str,'(.*:)(.*)( )(.*)$','\2') as short_course_id
  from tab

I prefer regexp_replace, because there are more possibilities to extract part of strings.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

One option would be 

select replace(regexp_substr(str,'[^:]+$'),
               regexp_substr(str,'[^:][^ ]+$'),'') as course_id
  from tab

Demo

where first regexp_substr() extracts the substring starting from the colon to the end, and the second one from the last space to the end.

Upvotes: 0

Andrew
Andrew

Reputation: 8703

If you don't want to mess with regex, you can use a combo of substr and instr.

select
substr(part1,1,instr(part1, ' ',-1,1) ) as course,
part1
from (
select
substr(<your column>,instr(<your column>,':',1,1) +1) as part1
from
<your table>
  ) t

Fiddle

Upvotes: 0

Related Questions