Regular Expression to check the time format

Database
KVB
Enthusiast

Regular Expression to check the time format

Hi

Recently we had a bad data coming from the source and storing as character field.Later they are converting to time format.

Meanwhile,I need to verify the format coming from the source.

eg:00:00:00--pass  , 25:78:78---should fail, any special characters should fail, and ':' should be strictly in 3rd and 6th position.

hope you understand,anything out of time format,should return false

Any help, appreciated.

Thanks

KVB

2 REPLIES
KVB
Enthusiast

Re: Regular Expression to check the time format

I have done the below one.

SELECT REGEXP_SIMILAR('00:00:00','[0-9]{2}:[0-9]{2}:[0-9]{2}','c')

But not sure that the time contains like 78:78:78,it also would pass.

Any help appreciated.

Junior Contributor

Re: Regular Expression to check the time format

Try

regexp_similar(col,'([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])')