REGEX replace in T-SQL

Question

I have a article table full with false descriptions. for example Ger-teschutz because somebody replaced all äs with -.

Now I want to get Geräteschutz instead of Ger-teschutz, but I also have other strings in there which have to stay that way, for example TX-40 or WA-I30.

But I only want to replace that one - in that string and not all of them.

I want to replace them by regex where the char before the - is upper case and after lower case.

Can anybody help me?

Solution

I want to replace them by regex where the char before the - is upper case and after lower case.

I'm not sure if this regex you describe will capture all of your data in the way you seem to intend in your example, but here is one possibility in SQL:

update MyTable
set MyColumn = left(MyColumn, patindex('%[A-Z]-[a-z]%', MyColumn collate Latin1_General_BIN))
                + 'ä'
                + right(MyColumn, len(MyColumn) - 1 - patindex('%[A-Z]-[a-z]%', MyColumn collate Latin1_General_BIN))
where MyColumn collate Latin1_General_BIN like '%[A-Z]-[a-z]%'

GeR-teschutz -> GeRäteschutz

Note that both like and patindex can understand character sets, much like regex. I am also specifically using a case-sensitive binary collation with each of them, as I don't know your database.

You'll also have to run this multiple times if there are multiple matches in one value ("GeR-tescH-tz").

This does not check for boundary cases that may exist in your data (word endings, etc.).

UPDATE: I've updated the query to use the more common character range for the sets, and used a binary collation. If a non-binary collation is necessary, one would have to put each letter in the set. source: How does SQL Server Wildcard Character Range, eg [A-D], work with Case-sensitive Collation?