It is unbelievable to me that Microsoft SQL Server does not support regular expressions. In the absences of regex, replacing multiple occurrences of the same string/char becomes super tedious. You can nest multiple Replace() statements which gets ugly and impossible to read and you have to know exactly how many multiple occurrences there are. But for a current project I have to replace all line breaks in a column.
Here’s how I did it:
WHILE EXISTS(SELECT * FROM #yourtable WHERE (Comments like '%'+char(10)+'%')) --note I knew that char(10) and char(13) always occurred next to each other and in an effort to speed up this loop got rid of the char(13) filter
BEGIN
UPDATE #yourtable
SET Comments=REPLACE(Comments,char(10),' ')
WHERE (Comments like '%'+char(10)+' %')
UPDATE #yourtable
SET Comments=REPLACE(Comments,char(13),' ')
WHERE (Comments like '%'+char(13)+' %')
-- again you can probably do this in one statment but I was hoping to speed it up by simplifying the where statements.
END
There are no doubt a dozen ways to optimize this but it seems to have worked and didn’t take forever. Good luck out there, folks, working with a “modern” RDMS that doesn’t support regex.
