Tuesday, March 2, 2010

How to remove newline chars in SQL

I'm working on the migration from a legacy system to a new one, and the source database is filled with presentation details like newline characters and HTML tags. To get rid of all this stuff (which doesn't belong in there) I used a simple yet effective custom function:

create function removeNewLineChars(@source varchar(150))
returns varchar(150)
return replace(replace(replace(@source, char(10), ' '), char(13), ' '), char(9), ' ')

As the source and target database are Microsoft SQL Server, T-SQL is the dialect of choice.

Let me say it again: presentation details do not belong in data, at least not in an enterprise system. Unluckily, you see it a lot in old applications (and, alas, even in some new ones). You can shoot me for that, but I won't change my mind.

No comments: