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
Comments
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