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.
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;
You can use the following regex for this:
#([^#]*)#=([^#]*)(?:;|$)
#([^#]*)#= - Will capture the "attribute". ([^#]*) - Get value (?:;|$) - Non-capture group that says the value will end with ; or "final de string"
See REGEX101
If you want to get all the possible sequences at once:
(#([^#]*)#=([^#]*)(?:;|$))+
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.
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*