New post
Question
· Jan 31, 2023

Cache SQL Query Regular Expression

#SQL #Caché

Hi,

      Can anyone please help me how to use regular expression/wild character on a sql query  for a pattern like C00.0 to C00.9, C01.0 to C01.9  and so on until C77.0 to C77.9  .

 

Thanks for your help

 

Thanks

Jude

Product version: IRIS 2021.1
Discussion (3)1
Log in or sign up to continue
Alex Woodhead · Feb 1, 2023

Hi Jude,

SELECT * FROM Test.Pattern
where ColumnABC %PATTERN '1"C"1(1"00",1"01",1"77")1"."1N'

Explanation:

  • First Character is "C"
  • Second and Third Character is one of "00", "01" or "77"
  • Fourth Character is "." (dot)
  • Fifth Character is numeric (0 -9)

If needing the full range between C00 to C77 then:

SELECT * FROM Test.Pattern
where ColumnABC %PATTERN '1"C"1(1(1"0",1"1",1"2",1"3",1"4",1"5",1"6")1N,1"7"1(1"0",1"1",1"2",1"3",1"4",1"5",1"6",1"7"))1"."1N'
  • First Character is "C"
  • Second and third character are from

Option 1 - Second character is "0" to "6"

                 Third character is numeric "0" to "9"

Option 2 - Second character is "7"

                 Third character is numeric "0" to "7"

  • Fourth Character is "." (dot)
  • Fifth Character is numeric (0 -9)

Some further notes. These patterns also work unmodified in IRIS Object Script so you might have code:

ClassMethod ProcessCodeInline(code) As %Boolean
{
      if code?1"C"1(1(1"0",1"1",1"2",1"3",1"4",1"5",1"6")1N,1"7"1(1"0",1"1",1"2",1"3",1"4",1"5",1"6",1"7"))1"."1N {
  Write !,"Matched code pattern"
} else {
  Write !,"No match of code pattern"
}
}

So same pattern for both your SQL and ObjectScript.

The pattern AND IF structure may get a bit visually daunting so you can use indirection operator "@" to move the pattern into a Class Parameter. This also allows you to reuse the same match expression but manage the expression in one place:

 Parameter codepattern = "1""C""1(1(1""0"",1""1"",1""2"",1""3"",1""4"",1""5"",1""6"")1N,1""7""1(1""0"",1""1"",1""2"",1""3"",1""4"",1""5"",1""6"",1""7""))1"".""1N"; 

ClassMethod ProcessCode(code) As %Boolean
{
 if code?@..#codepattern {
   Write !,"Matched code pattern" 
 } else {
   Write !,"No match of code pattern"
 }
}

If you see this double quoted pattern in parameter, code or in a configuration global, it is a clue it is applied in a pattern via indirection by the application.

 set ^matchme("LabFormat")="1""C""1(1(1""0"",1""1"",1""2"",1""3"",1""4"",1""5"",1""6"")1N,1""7""1(1""0"",1""1"",1""2"",1""3"",1""4"",1""5"",1""6"",1""7""))1"".""1N"
 if "C01.8"?@^matchme("LabFormat") Write !,"Matched code pattern"

Cheers,

Alex

0 0