7

I insert data that contains a line feed character into the database. Then I retrieve that data. I am using this script to attempt to remove the line feed while selecting the data from SQL:

Select Replace(Replace stringname,char(10),'',char(32),'')) from tablename

The replace function seems to execute, but it does not remove the line feed correctly.

yourcomputergenius
  • 303
  • 3
  • 11
charu
  • 389
  • 2
  • 4
  • 12
  • 2
    Shouldn't it be `Select Replace(Replace(stringname,char(10),''),char(13),'') from tablename`? Also you could use single replace `Select Replace(stringname,char(13)+char(10),'') from tablename`. char(32) corresponds to space symbol – user2316116 Feb 20 '15 at 12:00
  • Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). – John Saunders Feb 20 '15 at 12:48
  • Possible duplicate of [how i can remove all NewLine from a variable in SQL Server?](https://stackoverflow.com/questions/18073190/how-i-can-remove-all-newline-from-a-variable-in-sql-server) – Jesse Webb Dec 08 '17 at 20:17
  • Possible duplicate of [Replace a newline in TSQL](https://stackoverflow.com/questions/951518/replace-a-newline-in-tsql)? – Jesse Webb Dec 08 '17 at 20:19

5 Answers5

35

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

Jose Marfil
  • 645
  • 8
  • 14
  • Thanks for reply. yeah you are correct. I am using the same script. But it doesn't works. For example lets me take the string with char-code(10) i get is 'xxxx(3 spaces)xx' . when i test by replacing the charcode by '-', the retrieve replaces the '-' with charcode but it shows one more space like 'xxx -xx'). And also while excel export xxx comes in one line and -xx goes to second line. – charu Feb 20 '15 at 12:11
  • @charu, maybe you can try this: declare \@pos int; declare \@str nvarchar(255), \@res nvarchar(255); select \@str = 'a'+CHAR(10)+CHAR(13)+'abcd' set \@pos = 1; WHILE \@pos <= DATALENGTH(\@str) BEGIN print 'pos ' + convert(nvarchar(10),\@pos) + ' ascii:' + '['+CONVERT(nvarchar(10),ASCII(SUBSTRING(\@str, \@pos, 1)))+']' SET \@pos = \@pos + 1 END; GO to find out if you have some other ascii codes in your string rather than the ones you are triying to replace. – Jose Marfil Feb 20 '15 at 12:45
1

Shouldn't it be Select Replace(Replace(stringname,char(10),''),char(13),'') from tablename?

Also you could use single replace Select Replace(stringname,char(13)+char(10),'') from tablename.

char(32) corresponds to a space symbol

user2316116
  • 6,726
  • 1
  • 21
  • 35
0
(select Replace( (select REPLACE(   ColName,CHAR(10),'')),char(13),''))
   as ColAlias

from YourTable
Plexis Plexis
  • 302
  • 2
  • 12
  • 4
    While this code snippet may be the solution, [including an explanation](https://meta.stackexchange.com/questions/114762/explaining-entirely-%E2%80%8C%E2%80%8Bcode-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Narendra Jadhav Jun 08 '18 at 13:21
0

Solved with following . I had issues with
in sql data which even can not be seen as well in sql, causing me problem in some jquery functions.

A line feed is CHAR(10); a carriage return is CHAR(13).

The following code will remove a line feed characters and replaces it with a zero-length string:

UPDATE Table_Name SET Field_Name = REPLACE(Field_Name,CHAR(10),'');

Hassan Qasim
  • 463
  • 5
  • 5
0

If you want remove CRLF from the end of a string you can use RTRIM in postgresql.

for update operation:

UPDATE tablename
SET columnname = RTRIM(RTRIM(columnname,chr(10)),chr(13))
WHERE columnname like '%' || chr(13) or columnname like '%' || chr(10)

or for select:

SELECT RTRIM(RTRIM(columnname,chr(10)),chr(13)) FROM tablename

if you want leading or both use LTRIM or BTRIM

scientist
  • 141
  • 1
  • 5