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 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) || '%'
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),'')
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
You can also use regular expressions:
SELECT * FROM Parameters WHERE Name REGEXP '\n';
this works: select * from table where column like '%(hit enter)%'
Ignore the brackets and hit enter to introduce new line.
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]
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%'
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
Success story sharing
chr
instead ofchar
.||
used in a SQL string instead ofOR
. When is that a concern?||
in any of the SQL that I've done. I never really concern myself with ANSI SQL equivelents. I just code for the current database and be done with it. In my experience, you are never going to convert from one database vendor to another. Each vendor has added many extra SQL commands or variations beyond and/or in place of the ANSI standards, which make it virtually impossible to code anything complex and be 100% ANSI compatible.