SQL : Clear newlines characters (remove \r\n ) across all varchar and nvarchar fields in a SQL database

Posted: April 28, 2016 by Sagar Wasule in Others, Programming Concepts
Tags: , ,

In order to convert newlines (replace \r\n with \n) across all varchar and nvarchar fields in a database below is the simple query that will be helpful to generate queries to update the data in table.


select 'update ' + sc.name + '.' + t.name + ' set ' + c.name + ' = REPLACE(REPLACE(' + c.name + ', CHAR(13), ''''), CHAR(10), '')'
from sys.columns c
inner join sys.systypes st
on c.system_type_id = st.xtype
and CHARINDEX('varchar', st.name) <> 0
inner join sys.tables t
on c.object_id = t.object_id
inner join sys.schemas sc
on t.schema_id = sc.schema_id

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s