Menu
I am recovering from a bug in a system I built where I did not take into account that IE generates Windows-style newlines (\r\n) and other browsers generate Unix-style newlines (\n) when posting HTML forms with text areas. Now I need to convert all Windows-style newlines (\r\n) to Unix-style newlines (\n) throughout the varchar and nvarchar fields in my SQL-Server database.
Is there a way to iterate through all tables/rows in T-SQL and replace instances of '\r\n' with '\n' for varchar and nvarchar fields?
EDIT: I think the replace part would be something like
REPLACE(@fieldContents, CHAR(13)+CHAR(10), CHAR(10))
The hard part is doing this across all varchar and nvarchar fields.
773
CHR(10) is used to insert line breaks, CHR(9) is for tabs, and CHR(13) is for carriage returns. In the example above, we wanted to remove all occurrences of the line break, of the tab, and of the carriage return, so we used CHR(10) , CHR(9) , and CHR(13) .
Background. The "N" prefix stands for National Language in the SQL-92 standard, and is used for representing Unicode characters.
Solution. Use Virtual View to load the data to Data Vault, and in Virtual View, use the CONVERT() function of SQL Server to extract NVARCHAR(MAX) data as VARCHAR.
Remove and Replace Carriage Returns and Line Breaks in SQL Using SQL to remove a line feed or carriage return means using the CHAR function. A line feed is CHAR(10); a carriage return is CHAR(13).
Something like this? You could then dynamically execute these strings or just cut/paste the results and execute them in a query window.
select 'update ' + sc.name + '.' + t.name + ' set ' + c.name + ' = replace(' + c.name + ', CHAR(13)+CHAR(10), 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
