Replace certain pattern in a long string in MS SQL using T-SQL

Question

Hi all,

I have a table in my MS SQL database where it has some incomplete data in a field. This field in question is a varchar field and has about 1000 characters in the field. This string consists of segmentations of words in the format of a forward slash followed by the segment and then ends with a forward slash (i.e. /p/). Each of these segments would be separated by a space. The problem is that certain of these segmentations do not have the last forward slash (i.e. /p). I need to write a T-SQL script that would correct this problem.

I know I will need to use an update statement to do that. I got the where clause too. But the problem that I have is what am I setting it to equal to. Since the string has about 1000 characters, I don't want to type the actual string and just correct the problematic segmentation. My question is, is there a "RegEx replace function" that would only change problematic segmentations and leave the rest of the string alone?

Your help will be greatly appreciated.

Thanks in advance, Monte

Answer 1

SQL doesn't support RegEx within it. You could write a SQL CLR function then pipe the data through it and if there's a problem with the data correct it then return the corrected version to SQL.

UPDATE YourTable
    Set YourColumn = dbo.YourClrProc(YourColumn)

Answer 2

If you have Windows Scripting Host installed (most machines do), then you can use this method to call into the VBScript.RegExp object from T-SQL.

Answer 3

There is REPLACE, but is nothing close to RegEx.

If this is a one time operation then you can consider exporting the table, use a tool you're familiar with like sed or grep and then import the modified data back. It will probably be faster and more correct than trying to do this in T-SQL.

On the other hand if is a planned maintenance operation you'll need to repeat often as a way to maintain the data, then I concur with mrdenny, a CLR function is probably the best choice.