How do I remove line feed characters when selecting data from SQL Server?
The syntax of your statment looks wrong, maybe you can try with something like this:
Select Replace(Replace(@str,CHAR(10),''),CHAR(13),'')
The inner replace relaces LF and the outer replace replace CR
Replace a newline in TSQL
Actually a new line in a SQL command or script string can be any of CR, LF or CR+LF. To get them all, you need something like this:
SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
How to remove all new line characters from all tables/columns
This is similar to Sean Lange's answer, but it resolves to one update per table instead of one update per column.
--declare @schema nvarchar(256) = 'dbo';
--declare @table nvarchar(256) = 'table';
declare @sql nvarchar(max) = '';
set @sql += (select 'update '+t.table_schema+'.'+t.table_name+' set ' +stuff(
( select ', ['+i.column_name +']=replace(replace(['+i.column_name+'],char(10),''''),char(13),'''')'+char(10)
from information_schema.columns i
where i.table_schema=t.table_schema
and i.table_name=t.table_name
and i.data_type in ('char','nchar','varchar','nvarchar','text','ntext')
order by i.ordinal_position
for xml path('')),1,1,'')+';'+char(10)
from information_schema.tables t
where t.table_type='base table'
--and t.table_schema = @schema
--and t.table_name = @table
for xml path (''), type).value('.','varchar(max)')
--print @sql
select @sql
--exec sp_executesql @sql
How to check my data in SQL Server have carriage return and line feed?
You can use SQL Server's char(n)
and contains()
functions to match on field contents in your WHERE
clause.
carriage return: char(13)
line feed: char(10)
The following SQL will find all rows in some_table
where the values of some_field
contain newline and/or carriage return characters:
SELECT * FROM some_table
WHERE CONTAINS(some_field, char(13)) OR CONTAINS(some_field, char(10))
To remove carriage returns in your some_field
values you could use the replace()
function long with char()
and contains()
. The SQL would look something like:
UPDATE some_table
SET some_field = REPLACE(some_field, char(13), '')
WHERE CONTAINS(some_field, char(13))
To remove new lines you can follow up the last statement with the char(10) version of that update. How you do it all depends on what types of newlines your text contains. But, depending on where the text was inserted/pasted from the new lines may be \r\n or \n so running updates against both \r and \n characters would be safer than assuming that you're getting one version of newline or the other.
Note that if the newlines were removed and you want to retain them then you have to fix the issue at the point of entry. You can't replace or fix what has been removed so you should save the original text data in a new column that holds the original, unmodified text.
Remove only leading or trailing carriage returns
Find the first character that is not CHAR(13)
or CHAR(10)
and subtract its position from the string's length.
LTRIM()
SELECT RIGHT(@MyString,LEN(@MyString)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',@MyString)+1)
RTRIM()
SELECT LEFT(@MyString,LEN(@MyString)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',REVERSE(@MyString))+1)
SQL query for a carriage return in a string and ultimately removing carriage return
this will be slow, but if it is a one time thing, try...
select * from parameters where name like '%'+char(13)+'%' or name like '%'+char(10)+'%'
Note that the ANSI SQL string concatenation operator is "||", so it may need to be:
select * from parameters where name like '%' || char(13) || '%' or name like '%' || char(10) || '%'
Related Topics
Exclude a Column Using Select * [Except Columna] from Tablea
How to Update Millions of Records in MySQL
How to Find What Privileges a Group Has in Redshift
Postgresql Delete Multiple Rows from Multiple Tables
How to Select Multiple Values in One Field MySQL
Sql: Returning the Most Common Value for Each Person
Nodejs, MySQL - Json Stringify - Advanced Query
How to Select Oldest Date from MySQL
Sql Average from Multiple Columns
Selecting the First Day of the Month in Hive
How to Check If More Than One Column Is Not Null Per Record
How to Limit Results of a Left Join
Select and Compare Two Datetime Columns from Different Table Without Having Any Relation
Sql Take Just the Numeric Values from a Varchar
Formatting Numbers by Padding With Leading Zeros in SQL Server
How to Extract Part of a String in Hive