Meenakshi Shanmugam · Jul 3, 2020

Need help in using Regex in Cache DB SQL


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.


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

If I understand you correctly,  you want something like

select * from yourTablename where yourColumname %PATTERN '1.N'

i.e. where a column contains an integer greater or equal 0 (1.N means: one or more digits)

Thanks . It is working fine. Also I want to find if date column is in MM/DD/YYYY format. How can I do that in sql? Appreciate your help.

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?

I think it is option b. I want to check If it is in MM/DD/YYYY format.

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