question

dduser avatar image
dduser asked

How to find in which columns exists carriage return ?

I need to find in WHICH column(s) have carriage return of a given table ?
column
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

For additional resources, we had a similar question a while back on this site. You can replace the search term in my answer with char(13) to search all character-based columns of all tables for the carriage return: https://ask.sqlservercentral.com/questions/29608/looking-for-a-needle-in-an-sql-haystack.html @TimothyAWiseman also had an answer on that thread and his code targets a single table rather than every table, so might be just what you're looking for. He linked to his article/code on SSC here: http://www.sqlservercentral.com/articles/Search/64877/
0 Likes 0 ·
Hi KenJ I used FindValue proc, but how to find carriage return exits? This not returns the correct output, but returns correctly for a normal string character. Exec FindValue @TableName = 'contacts', @Value = 'char(13)', @schema = 'dbo'
0 Likes 0 ·
char(13) should not have quotes
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
UPDATE Sligthly rewritten. Now returning one column per text-datatype-column in the table, and with values 'Y' and 'N' for having carriage return or not. Plus changed the part where I remove the trailing ', ' to use LEN(N', ') instead of the constant 2, because I didn't get it to work when I copy/pasted the code (it didn't remove the trailing characters correctly). DECLARE @sql nvarchar(max)=N'SELECT '; DECLARE @tablename sysname; SET @tablename = 'Production.Document'; SELECT @sql = @sql + 'CASE WHEN MAX(CHARINDEX(CHAR(13),' + QUOTENAME(c.name) + ')) > 0 THEN ''Y'' ELSE ''N'' END as ' + QUOTENAME(c.name) + ', ' FROM sys.columns c WHERE object_id = OBJECT_ID(@tablename) AND c.system_type_id IN (35, 99, 167, 175, 231, 239) IF @sql N'SELECT ' BEGIN SET @sql = LEFT(@sql,LEN(@sql)-LEN(N', ')) + ' FROM ' + @tablename; EXEC sp_executesql @sql print @sql END ELSE BEGIN RAISERROR('No text column defined on table %s',16,1,@tablename); END To make Dave Greens solution more generic, one could make use of some dynamic SQL, querying the sys.columns view. Something like this: OLD CODE REMOVED
23 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I got error Msg 50000, Level 16, State 1, Line 24 No text column defined on table But there are many records found in the table. It is a varchar (1000) field, and there are many coulmns cannot find 1 by one since there are many columns (nearly 100)
0 Likes 0 ·
And you did replace SET @tablename='Production.ScrapReason' with your own tablename?
0 Likes 0 ·
yes I changed that
0 Likes 0 ·
Can you check what you have in sys.columns for that table? SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(@tablename) Specifically, check which system_type_id you have for the columns which should be text-datatypes.
0 Likes 0 ·
I tried it with a table that had two varchar columns and it didn't work for me
0 Likes 0 ·
What do you mean it didn't work? Weren't you able to look in sys.columns or did my script not work?
0 Likes 0 ·
Show more comments
Dave_Green avatar image
Dave_Green answered
Something like this should tell you which columns have a carriage return in any row of that column: SELECT CASE WHEN MAX(CHARINDEX(CHAR(13),mycolumn)) > 0 THEN 'mycolumn; ' ELSE '' END + CASE WHEN MAX(CHARINDEX(CHAR(13),mysecondcolumn)) > 0 THEN 'mysecondcolumn; ' ELSE '' END etc. (more discussion on this usage on [this SQL Team forum post][1]) [1]: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55855
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.