Extract a number from a string after specific string in Oracle SQL [duplicate]

Question

I would like to extract from this string: FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=7;BYMINUTE=0;BYSECOND=0 the number that is after 'BYHOUR=' string using Oracle pl/sql.
It can be 1 or 2 character long (0-23)
It is probably best to use regex regular expression but I was never fluent in creating them.

Can someone help and explain it?

Tim Biegeleisen · Accepted Answer · 2016-08-24 14:23:48Z

2

Use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(col, 'BYHOUR=(\d{1,2})')
FROM yourTable

answered Aug 24, 2016 at 14:23

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

4 Comments

Aleksander Lipka Over a year ago

Thanks, it worked, the output though is: 'BYHOUR=12'. Is it possible to get only a number in this statement (using regex) or do I have to use substr() method to get it?

Chrisrs2292 Over a year ago

I think you could change it to this REGEXP_SUBSTR(col, '(\d{1,2})',instr(col,'BYHOUR'))

Bohemian Over a year ago

@chris See duplicate question's answer

daya Over a year ago

select to_number(substr(REGEXP_SUBSTR(col, 'BYHOUR=(\d{1,2})'), 8)) from your table

Collectives™ on Stack Overflow

Extract a number from a string after specific string in Oracle SQL [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related