All Forums Database
KVB 124 posts Joined 09/12
06 Nov 2014
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

KVB 124 posts Joined 09/12
06 Nov 2014

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.

dnoeth 4628 posts Joined 11/04
06 Nov 2014

Try

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

 

Dieter

You must sign in to leave a comment.