SQL query for a carriage return in a string and ultimately removing carriage return

Question

SQL query for a carriage return in a string and ultimately removing carriage return  I have some data in a table and there are some carriage returns in places where I don t want them  I am trying to write a query to get all of the strings that contain carriage returns   I tried this  select   from Parameters where Name LIKE     n       Also  select   from Parameters where Name LIKE   r       Both are valid SQL but are not returning what I am looking for  Do I need to use the Like command or a different command  How do I get the carriage return into the query   The carriage return is not necessarily at the end of the line either  may be in the middle

User · Accepted Answer

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

User · Answer

Something like this seems to work for me   SELECT   FROM Parameters WHERE Name LIKE    n

User · Answer

this works  select   from table where column like    hit enter     Ignore the brackets and hit enter to introduce new line

User · Answer

The main question was to remove the CR LF  Using the replace and char functions works for me   Select replace replace Name char 10      char 13        For Postgres or Oracle SQL  use the CHR function instead          replace replace Name CHR 10      CHR 13

User · Answer

You can also use regular expressions   SELECT   FROM Parameters WHERE Name REGEXP   n

User · Answer

Omit the double quotes from your first query       LIKE    n

User · Answer

If you are considering creating a function  try this          DECLARE            schema sysname    dbo             tablename sysname    mvtEST             cmd NVarchar 2000             ColName sysname      DECLARE  NewLine Table      ColumnName Varchar 100       Location Int      ColumnValue Varchar 8000             SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE  TABLE SCHEMA    schema AND TABLE NAME     tablename AND DATA TYPE LIKE   CHAR        DECLARE looper CURSOR FAST FORWARD for     SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA    schema AND TABLE NAME     tablename AND DATA TYPE LIKE   CHAR       OPEN looper     FETCH NEXT FROM looper INTO  ColName      WHILE   fetch status   0     BEGIN     SELECT  cmd    select       ColName           CHARINDEX Char 10         ColName           ColName     from    schema        tablename    where CHARINDEX Char 10         ColName       gt  0 or CHARINDEX CHAR 13      ColName      gt  0      PRINT  cmd     INSERT  NewLine   ColumnName  Location  ColumnValue       EXEC sp executesql  cmd     FETCH NEXT FROM looper INTO  ColName     end     CLOSE looper     DEALLOCATE looper       SELECT   FROM   NewLine

User · Answer

In SQL Server I would use    WHERE CHARINDEX CHAR 13   name   lt  gt  0 OR CHARINDEX CHAR 10   name   lt  gt  0   This will search for both carriage returns and line feeds   If you want to search for tabs too just add   OR CHARINDEX CHAR 9   name   lt  gt  0

User · Answer

This also works  SELECT TRANSLATE STRING WITH NL CR  CHAR 10     CHAR 13         FROM DUAL

User · Answer

You can create a function   CREATE FUNCTION dbo  Check existance of carriage return line feed           String VARCHAR MAX    RETURNS VARCHAR MAX  BEGIN DECLARE  RETURN BOOLEAN INT   WITH N1  n  AS  SELECT 1 UNION ALL SELECT 1   N2  n  AS  SELECT 1 FROM N1 AS X  N1 AS Y   N3  n  AS  SELECT 1 FROM N2 AS X  N2 AS Y   N4  n  AS  SELECT ROW NUMBER   OVER ORDER BY X n  FROM N3 AS X  N3 AS Y   SELECT  RETURN BOOLEAN  COUNT    FROM N4 Nums WHERE Nums n lt  LEN  String  AND ASCII SUBSTRING  String Nums n 1    IN  13 10       RETURN  CASE WHEN  RETURN BOOLEAN  gt 0 THEN  TRUE  ELSE  FALSE  END  END GO   Then you can simple run a query like this   SELECT column name  dbo  Check existance of carriage return line feed   column name  AS  Boolean  FROM  table name

[sql] SQL query for a carriage return in a string and ultimately removing carriage return

Examples related to sql

Examples related to char

Examples related to carriage-return

Examples related to sql-like