New post
Question
· Apr 11, 2017

How use REGEX (Regular Expression) IN SQL statement?

#SQL #Caché

Hi guys.

 

I need validate a field string in a table. It needs have a specific mask

I want use regex + CASE expression

There is any function to this purpose in SQL?

Discussion (5)1
Log in or sign up to continue
Timothy Leavitt · Apr 11, 2017

If you're looking to add RegEx-based validation to a property, see this post.

If you're looking to do RegEx matching in a query... I don't think there's a built in function for this (!), but it's easy enough to do in a stored procedure:

ClassMethod MatchesRegEx(pText As %String, pRegEx As %String) As %Boolean [ SqlProc ]
{
    Quit $Match(pText,pRegEx)
}
3 0
Amir Samary · Aug 8, 2017

I think you are looking for the %MATCHES SQL extension of ours:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

But it looks like this is a simplified regex not real regular expressions. But it may be powerful enough to accomplish what you need. Another option is the %PATTERN that allows you to use Caché's native pattern matching.

0 0
Anderson Farias · Dec 1, 2022

Text in English and Brazilian Portuguese / Texto em inglês e português brasileiro

English

Hello guys.

Years passed, and coincidentally a colleague of mine had the same question, and he found this post.
It was funny that he came up to me and asked me if I was the author of the post and how I got around the problem.

I found it interesting to review the post and add a real example

The problem
There is a text field that stores codes, my colleague wants to filter only records that have codes like this: '5000027-25.2022.8.25.0075'

For this he intended a regex like this: '\d{7}-\d{2}.\d{4}.8.25.\d{4}'

The question is how to do something like this in a sql query in Caché:

/*pseudo-code*/
SELECT *
FROM dummy
WHERE code = '\d{7}\-\d{2}\.\d{4}\.8\.25\.\d{4}'

The solution
The solution was to translate the regex to the %PATTER operator format

SELECT *
FROM dummy
WHERE code %PATTERN  '7N1"-"2N1"."4N1".8.25."4N'

WTF ?
How the hell did'\d{7}\-\d{2}\.\d{4}\.8\.25\.\d{4}' become'7N1"-"2N1"."4N1".8.25."4N' ?

So you can understand, I've broken down the pattern, and put the translation details in the table below

REGEX THE MEANING PATTERN
\d{7} 7 dígitos 7N
\- 1 string literal -(traço) 1"-"
\d{2} 2 dígitos 2N
\. 1 string literal .(ponto) 1"."
\d{4} 4 dígitos 4N
\.8\.25\. 1 string literal .8.25. 1".8.25."
\d{4} 4 dígitos 4N

Note that the logic is simple, but unfortunately the syntax, where all the pattern operators are concatenated, is horrible :(

[%PATTERN (SQL)] (https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...)

I hope I helped someone with this post ;)

Brazillian Portuguese

Olá pessoal.

Anos se passaram, e coincidentemente um colega meu teve a mesma dúvida, e ele achou este post.
Foi engraçado ele vir até mim e me perguntar se eu era o autor do post e como eu contornei o problema.

Achei interessante rever o post e adicionar um exemplo real

O problema
Há um campo do tipo texto que armazena códigos, o meu colega quer filtrar apenas os registros que possuam códigos como este: '5000027-25.2022.8.25.0075'

Para isso ele pretendia um regex como esse: '\d{7}-\d{2}.\d{4}.8.25.\d{4}'

A questão é como fazer algo assim em uma query sql no Caché:

/*pseudo-code*/
SELECT *
FROM dummy
WHERE code = '\d{7}\-\d{2}\.\d{4}\.8\.25\.\d{4}'

A solução
A solução foi traduzir o regex para no formato do operador %PATTER

SELECT *
FROM dummy
WHERE code %PATTERN  '7N1"-"2N1"."4N1".8.25."4N'

WTF ?
Como diabos isso '\d{7}\-\d{2}\.\d{4}\.8\.25\.\d{4}' virou isto '7N1"-"2N1"."4N1".8.25."4N' ?

Para poderem entender, eu destrinchei o padrão, e coloquei os detalhes da tradução na tabela abaixo

REGEX O SIGNIFICADO PATTERN
\d{7} 7 dígitos 7N
\- 1 string literal -(traço) 1"-"
\d{2} 2 dígitos 2N
\. 1 string literal .(ponto) 1"."
\d{4} 4 dígitos 4N
\.8\.25\. 1 string literal .8.25. 1".8.25."
\d{4} 4 dígitos 4N

Observem que a lógica é simples, mas infelizmente a sintaxe, onde todos os operadores do padrão ficam concatenados, é horrível :(

[%PATTERN (SQL)] (https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...)

Espero ter ajudado alguém com este post ;)

1 0