Find record with regular expression in mssql

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 find them, I need to find the records that have ; and soon after anything other than #

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

The database is MSSQL.

    
asked by anonymous 11.01.2018 / 13:31

3 answers

1

In SQL Server there is no native regular expression implementation. What comes closest is the default setting ( pattern ), used in LIKE and in the PATINDEX .

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

As an example, the table name is considered to be EAV, and the column name is AttribValue. Here are two ways to get the rows that have ; intrusive.

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

and

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

You can use the following regex for this:

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

Explanation

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

See REGEX101

Addendum

If you want to get all the possible sequences at once:

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

In SQL Server

I've never used Sql Server, so I do not know exactly how you apply, but here are two links that I believe potion to help.

How to use the REGEX in an SQL SELECT query
#

12.01.2018 / 12:52
-1

To find these records that you put in the question you can use a regex like the one proposed by Valdeir Psr in the comments:

;[^#]

It is worth noting that this regex does not capture cases equal to this: #aaaa#=1111;;# ...

I suggest you use this select to identify cases where ; is in the middle or the end of the value or name:

SELECT *, 
  CASE WHEN [campo] LIKE '.*;;#.*|^#.*?#.*?;[^#].*' 
    THEN 'Match' 
    ELSE 'No Match' 
  END Validates
FROM 
  *table*

You can test this regex here

    
11.01.2018 / 18:20