Need help in using Regex in Cache DB SQL

Question

Question

Meenakshi Shanmugam · Jul 3, 2020

#SQL #Caché #InterSystems IRIS

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

Benjamin De Boe · Jul 9, 2020

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

0 0

score 0 · Answer 1 · 2020-07-03T19:10:43-04:00

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)

score 0 · Answer 2 · 2020-07-06T17:41:32-04:00

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.

score 0 · Answer 3 · 2020-07-06T18:31:03-04:00

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?

score 0 · Answer 4 · 2020-07-06T19:20:32-04:00

Meenakshi Shanmugam · Jul 6, 2020

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

0 0