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).
This question is related to
sql
char
carriage-return
sql-like
Omit the double quotes from your first query.
... LIKE '%\n%'
This also works
SELECT TRANSLATE(STRING_WITH_NL_CR, CHAR(10) || CHAR(13), ' ') FROM DUAL;
Something like this seems to work for me:
SELECT * FROM Parameters WHERE Name LIKE '%\n%'
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),'')
this works: select * from table where column like '%(hit enter)%'
Ignore the brackets and hit enter to introduce new line.
You can also use regular expressions:
SELECT * FROM Parameters WHERE Name REGEXP '\n';
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]
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
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
Source: Stackoverflow.com