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) || '%'
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.
PL SQL Remove Non Ascii character but not carriage returns
Change the regexp to (not printable OR carriage return/newline).
WITH t (txt) AS
(
SELECT 'Hello'||chr(13)||' World' FROM DUAL
)
select REGEXP_REPLACE(txt, '[^[:print:|\x0A|\x0B|`\x0D]]', '') from t;
REGEXP_REPLA
------------
Hello
World
Nicely explained here
Carriage Return isn't being removed from exported .csv in Access
Try this using two steps:
Replace(Replace([field name], Chr(13), ""), Chr(10), "")
Replace all CRLF with \r\n using REXEXP_REPLACE
You need to concatenate the two characters you want to replace, not just put them in parentheses (even if you supplied both):
SELECT REPLACE(v_text, chr(13) || chr(10) , '\r\n')
INTO v_outtext
FROM dual;
If if it's already in a PL/SQL variable you don't need to to the context switch to select from dual; you can simplify to:
v_outtext := REPLACE(v_text, chr(13) || chr(10) , '\r\n');
but it sounds like you can use the first method to read the value from the database into v_outtext
in one go, without the intermediate v_text
.
db<>fiddle
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 carriage returns and line feeds from a column?
Your query retrieve from your table named TABLE all rows with the column replaced.
About UPDATE your database you must use UPDATE command in this way:
UPDATE table SET column = replace(replace(column,CHAR(13),''),CHAR(10),'')
If you want condition the UPDATE about the satisfaction of some conditions, so you must add the WHERE clause.
For example
UPDATE table SET column = replace(replace(column,CHAR(13),''),CHAR(10),'')
WHERE column_2 = 'XXX'
Related Topics
How to Compare Software Versions Using SQL Server
Why No Output When Plsql Anonymous Block Completes
How to Do Select Unique with Linq
How to Execute a Stored Procedure Inside a Select Query
SQL Error: Ora-02291: Integrity Constraint
How to Include Excluded Rows in Returning from Insert ... on Conflict
Exporting a Clob to a Text File Using Oracle SQL Developer
What's the R Equivalent of SQL's Like 'Description%' Statement
Rodbc SQLquery() Returns Varchar(255) When It Should Return Varchar(Max)
Stored Procedure Exec VS Sp_Executesql Difference
Count(Id) VS. Count(*) in MySQL
Invoking a Large Set of SQL from a Rails 4 Application
Rails Union Hack, How to Pull Two Different Queries Together
Rails .Where() Query Not Working