Question
· Jul 3, 2020

Need help in using Regex in Cache DB SQL

Hi,

Could someone help me in identifying all characters in a column is whole number 0 or greater

 

CASE WHEN '123' %MATCHES '[0-9]*' then 'VALID' else 'INVALID' end

 

The above statement is finding only for first character.

Thanks

Discussion (5)0
Log in or sign up to continue

As always, it depends on ...
In this case on the definition of the date column.

a) if the date column is defined as %Date (i.e.: Property DateOfBirth As %Date) then the STORED value is just a number like 65567, because dates are stored in $Horolog format). 

b) if the date column is defined (for whatever reason) as %String (i.e.: Property DateOfBirth As %String) then the stored value is (as the type says) a string, and you can use the pattern operator to check the format.So the first question is, how is your date column defined?

if that column is currently in a string format (you can tell from the column/resultset metadata), you can use a different pattern to check if it meets the raw format:

SELECT CASE WHEN '01/01/2020' %PATTERN '1.2N1"/"1.2N1"/"4N' THEN 'valid' ELSE 'invalid' END

if you also want to check if that resolves to a valid date, you can use CAST or TO_DATE, but those will throw errors if they don't resolve to a valid date, so a small custom function in ObjectScript is perhaps not a bad idea