Find regular expression record in mssql

Asked

Viewed 1,441 times

1

I have a column that stores data in the following pattern #campo#=valor;#campo#=valor (and so on)

ex: '#aaaa#=1111;#bbbbb#=2222;#cccc#=33333'

However, they added ; within the value and I wanted to locate them, I need to find the records they hold ; and soon after anything other than the #

ex of record to be found: '#aaaa#=1111;#bbbbb#=2222;#cccc#=33;333'

The database is MSSQL.

sql-server regex
  • 1

    You can use the following regex: ;[^#] and regex you can use based on this query: https://stackoverflow.com/a/29207975/9101590

2 answers

1


In SQL Server there is no native implementation of regular Expression. What comes closer is the definition of pattern (Pattern), used in LIKE and in function SKATEBOARD.

The way to store attributes and values seems to be an implementation of data modeling EAV.

As an example, it is considered that the table name is EAV and that the column name is Atribvalor. Here are two ways to obtain the lines that have ; intruder.

-- código #1
SELECT AtribValor
  from EAV
  where AtribValor like '%;[^#]%';

and

-- código #2
SELECT AtribValor
  from EAV
  where patindex('%;[^#]%', AtribValor) > 0;

0

You can use the following regex for this :

#([^#]*)#=([^#]*)(?:;|$)

Explanation

  • #([^#]*)#= - It will capture the "attribute".
  • ([^#]*) - Takes the value
  • (?:;|$) - No capture group that says the value will end with ; or "final de string"

See in REGEX101

Addendum

If you want to take all possible sequences at once :

(#([^#]*)#=([^#]*)(?:;|$))+

In SQL-Server

I’ve never used Sql Server, so I don’t know exactly how to use it, but here are two links that I believe will help.

How to use the REGEX in an SQL SELECT query
SQL Server Regex

Browser other questions tagged sql-server regex

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