Regex within SQL Server

Navigation
6

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 mounted a Regex that is working perfectly: (^[a-z]{1})([0-9]{3})(\W)([0-9]{4}) but I can not apply it to SQL Server, I tried using LIKE as in that 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, is there a way to do a query using a Regex?

Here is an image of Regex giving match:

    
asked by anonymous 07.12.2017 / 15:21

1 answer

5

Rate 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 search pattern can be in any position of the column, 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 natively T-SQL 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:

07.12.2017 / 21:03
How to implement a "scroll hijacking" What is SPA and what differs from a non SPA page?