sql - 在表的所有列中查找字符串值“n/a”

Question

我想在整个表中搜索，其中字符串为“NA”、“na”或“N/A”。实际上，该值应该是“n/a”，但被不同的部门错误地放置为“NA”、“na”或“N/A”。我使用了以下代码。但问题是，它也返回了“n/a”的所有值。我认为 'COLLATE Latin1_General_CS_AS NOT LIKE '%n/a' 可以在代码中的某处使用。但我迷路了，找不到出路。还有一种方法可以为变量 @stringtofind 放置三个值，以便我可以同时搜索 N/A、na 和 NA？谢谢并恭祝安康。

-妈妈

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

标签： sqltry-catchsqlcommandsql-scripts

Answer 1

1. 使用 COLLATE SQL_Latin1_General_CP1_CS_AS 进行区分大小写的搜索。

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