Regex inside SQL Server

Asked

Viewed 16,562 times

7

How to recover fields with the following pattern?

a111/1111 or a111_1111 or a111-1111 or a111+1111

where:

a = some letter of the alphabet;

1 = any digit of 1-9;

I rode a Regex that is working perfectly: (^[a-z]{1})([0-9]{3})(\W)([0-9]{4}) but I can’t apply it to SQL Server, I tried using LIKE as in this question example:

SELECT * FROM Table WHERE Campo like '%(^[a-z]{1})([0-9]{3})(\W)([0-9]{4})%'

or

SELECT * FROM Table WHERE Campo like '%[(^[a-z]{1})([0-9]{3})(\W)([0-9]{4})]%'

but it does not work, there is a way to make a query using a Regex?

Follow a picture of Regex giving match: inserir a descrição da imagem aqui

sql-server regex

  • The separator is only one of the characters / _ - and +? You used W in the expression.

  • Is that /W ended up serving, because if it was another number in the place of the special character o ([0-9]{3}) would not give match because it asks only 3 and not 4

  • So the fifth character is anyone, as long as it’s not a digit?

1 answer

6


Evaluate the following solution:

-- código #1 v2
SELECT colunas
  from tabela
  where coluna like '[a-z][0-9][0-9][0-9][/_+-][0-9][0-9][0-9][0-9]';

If the pattern sought can be in any column position, here is a variant of the above code:

-- código #2 v2
SELECT colunas
  from tabela
  where coluna like '%[a-z][0-9][0-9][0-9][/_+-][0-9][0-9][0-9][-9]%';

While T-SQL natively does not offer regular expressions, it is possible to implement through CLR routines. Here is an article that provides an option: CLR Assembly Regex Functions for SQL Server.

Documentation:

  • 1

    So I can’t use that counter regex feature ?

  • Exemplo quero 8 digitos ai eu uso [0-9]{8} ao invés de [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]

  • 3

    @Leonardobonetti To my knowledge, in T-SQL it is not possible to use {} to indicate the number of occurrences.

  • I’ll mark it as correct, the only thing that has become incorrect is [/+-] because here it would apply in a code A111/+-1111, it is correct to use an OR, but your answer helped 100%

  • @Leonardobonetti As for the fifth character, I used [/+-] because they are the characters in the example. If it can be any character, except digit, try replacing [/+-] by [ 0-9]

  • Would there be any way to get, for example, records that have only a semicolon? Let’s assume I want to return everything inside a varchar column that has only commas or only dots, without any letters.

Show 1 more comment

Browser other questions tagged sql-server regex

You are not signed in. Login or sign up in order to post.