Update CR LF in the string as empty

Question

Update CR LF in the string as empty

Saran

Right there with Babe

Points: 760
More actions
January 9, 2023 at 4:31 am
Go to Answer
#4135266
Hi All,
I have table which has data with lot of CR LF, Is it possible to update that string as empty.
- This topic was modified 1 year, 11 months ago by Saran.
Jeff Moden

SSC Guru

Points: 1004406
More actions
January 9, 2023 at 8:07 am
Answer
#4135331
Yes. The following will do the trick for LfCr, CrLf, Cr, and Lf... Just replace the table name and the column name with your actual table and column names.
```
 UPDATE dbo.YourTableNameHere
    SET YourColumNameHere = REPLACE(REPLACE(YourColumNameHere,CHAR(13),''),CHAR(10),'')
  WHERE YourColumNameHere LIKE CONCAT('%[',CHAR(13),',',CHAR(10),']%')
;
```
It will only replace the Cr and Lf characters with empty strings. Any other characters will come out unscathed. You'll need to play a bit if you want a space for one or the other character or a single space for both but I'm sure you get the idea here.
Please see the following references for more information on the world of ASCII character numbers...
https://learn.microsoft.com/en-us/sql/t-sql/functions/char-transact-sql
https://www.lookuptables.com/text/ascii-table
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

but why do that update? if the data is there it likely has a good reason for it and should not be changed on a whim. (specially not if it is only so you can view it on notepad!!!)

Saran Right there with Babe Points: 760 More actions · Answer 2

CR LF is breaking my data load into mysql, I have updated CR LF as space ' ' and load is working good.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Saran wrote:

CR LF is breaking my data load into mysql, I have updated CR LF as space ' ' and load is working good.

To Frederico's point, it may be that would be that the "data load" in MySQL is what actually needs to be fixed. On the other hand, any kind of formatting being stored in the DATA in a DATA base is usually a bad idea, IMHO. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

Saran wrote:

CR LF is breaking my data load into mysql, I have updated CR LF as space ' ' and load is working good.

it is not breaking it - you just need to do the load in a way that the issue isn't there - I (and others) gave you some options for it.

one of them (possibly the easiest for you) was to use a different line/row separator on both export and import. Did you do it?

and regardless of it you DO NOT update data like that just because a load to another system fails (incorrectly) - you replace it on the extract with something that you can then update back to what it was originally once the data is loaded (or as part of the load)

This reply was modified 1 year, 11 months ago by frederico_fonseca.

Saran Right there with Babe Points: 760 More actions · Answer 5

Thank you, I am planning the same to update some symbol.

I have tried different line/row separator, could not find how to export properly. The load data is working good if it does not have any breaking. Sometimes, I manually update notepad. But after fixing CR LF no manual update is needed, some places I see gibberish symbol ex - ÿ. I am manually removing those gibberish.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

Saran wrote:

Hi All,
I have table which has data with lot of CR LF, Is it possible to update that string as empty.

Quick question, what have you set as the default-character-set for the import?

😎

Saran Right there with Babe Points: 760 More actions · Answer 7

Saran

Right there with Babe

Points: 760

January 10, 2023 at 3:01 pm

#4135971

Yes, default.

utf8mb4 - utf8mb4_0900_ai_ci

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 8

You might want to replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“.

😎

Question: have you checked that the binary format of the file matches the settings?

For UTF-8, the BOM (byte order mark) should be EF BB BF in HEX or ï»¿ in text.