CODEWITHSUNDEEP
sqltry-catchsqlcommandsql-scripts
mumid
mumid

Reputation: 17

find a string value 'n/a' in all columns of a table

I would like to search in entire table, where the string is 'NA', 'na' or 'N/A'. Actually, the value was supposed to be 'n/a' but it was mistakenly placed as 'NA', 'na' or 'N/A' by different department. I used the following code. But the problem is, it is returning all the values for 'n/a' as well. I think 'COLLATE Latin1_General_CS_AS NOT LIKE '%n/a' can be used somewhere in the code. But I am lost and can't find my way out of this. Also is there a way to place three value for variable @stringtofind so that I can search for N/A, na and NA at the same time? Thanks and regards.

-Mumid

'''DECLARE
 @stringToFind VARCHAR(100) = 'N/A', 
 @schema sysname = 'dbo', 
 @table sysname = 'SourceToTargetMapping'

BEGIN TRY
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' 

   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @stringToFind + ''' OR '
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = @schema
   AND TABLE_NAME = @table 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   EXEC (@sqlCommand)
   PRINT @sqlCommand
END TRY

BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
END CATCH '''

Upvotes: 0

Views: 299

Answers (1)

Atk
Atk

Reputation: 752

  1. Use COLLATE SQL_Latin1_General_CP1_CS_AS for case sensitive search.
DECLARE
 @stringToFind VARCHAR(100) = 'C1', 
 @schema sysname = 'dbo', 
 @table sysname = 'TicketValue'

BEGIN TRY
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' 

   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + '%'+@stringToFind +'%'+''' COLLATE SQL_Latin1_General_CP1_CS_AS '+ ' OR '
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = @schema
   AND TABLE_NAME = @table 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   EXEC (@sqlCommand)
   PRINT @sqlCommand
END TRY

BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
END CATCH

Upvotes: 0

Related Questions