CODEWITHSUNDEEP
sqlcharcarriage-returnsql-like
Maestro1024
Maestro1024

Reputation: 3293

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

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).

Upvotes: 73

Views: 319445

Answers (10)

Habib
Habib

Reputation: 70

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 +' ) > 0 or CHARINDEX(CHAR(13), '+@ColName +') > 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

Upvotes: 0

Amit Tikoo
Amit Tikoo

Reputation: 167

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<=LEN(@String) AND ASCII(SUBSTRING(@String,Nums.n,1)) 
IN (13,10)    

RETURN (CASE WHEN @RETURN_BOOLEAN >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]

Upvotes: 1

HLGEM
HLGEM

Reputation: 96572

In SQL Server I would use: 

WHERE CHARINDEX(CHAR(13), name) <> 0 OR CHARINDEX(CHAR(10), name) <> 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) <> 0

Upvotes: 14

Logan
Logan

Reputation: 41

this works: select * from table where column like '%(hit enter)%'

Ignore the brackets and hit enter to introduce new line.

Upvotes: 4

Grant Dever
Grant Dever

Reputation: 801

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),'')

Upvotes: 80

djc
djc

Reputation: 11711

Something like this seems to work for me:

SELECT * FROM Parameters WHERE Name LIKE '%\n%'

Upvotes: 0

KM.
KM.

Reputation: 103587

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) || '%'

Upvotes: 95

user2394206
user2394206

Reputation: 1

This also works

SELECT TRANSLATE(STRING_WITH_NL_CR, CHAR(10) || CHAR(13), '  ') FROM DUAL;

Upvotes: 0

JYelton
JYelton

Reputation: 36512

You can also use regular expressions:

SELECT * FROM Parameters WHERE Name REGEXP '\n';

Upvotes: 3

Byron Whitlock
Byron Whitlock

Reputation: 53861

Omit the double quotes from your first query.

... LIKE '%\n%'

Upvotes: 0

Related Questions