CODEWITHSUNDEEP
sqlregexoracle-databaseplsql
O P
O P

Reputation: 2365

SQL regular expression to get number

I have these time fields in my db:

0400 - 0530
0430 - 0530
0930 - 1050
1000 - 1130
1400 - 1450

Users can input 2 characters for a start time. 04 would then return:

0400 - 0530
0430 - 0530

I currently use a check to get the start time: to_char(a.time, 'hh24')

I want to be able to modify this so I am actually getting times in that range. For example, 10 would return:

1000 - 1130

I need it to return:

0930 - 1050
1000 - 1050

My thought is to use a regular expression to get the 2 digits after -. How can I achieve this?

Upvotes: 0

Views: 137

Answers (2)

Braj
Braj

Reputation: 46841

You can try

^(\d{4} - 10\d{2}|10\d{2} - \d{4})$

Here is online demo

Use [0-9] in place of \d if not supported.

Sample query

WHERE REGEXP_LIKE(column_name, '^(\d{4} - 10\d{2}|10\d{2} - \d{4})$')

Have a look at

Upvotes: 1

woemler
woemler

Reputation: 7169

If you just need to check to see if the input digits, which look like they represent the hour-portion of the time, match either the start or end hours, you can try:

select * from table where
regexp_substr(time_string, '^\d\d') = input_string
or regexp_replace(regexp_substr(time_string, '-\s*\d\d'), '-\s*', '') = input_string

Or if you would like to try matching the actual DATE columns, you can try something like:

select * from table where
to_char(start, 'HH24') = input_string
or to_char(end, 'HH24') = input_string

Upvotes: 1

Related Questions