claude-opus-4-20250514-thinking-16k· Anthropic LMArena Human Preference 140K
Build me a dynamic SQL query that will search all columns in a particular table for the…
- User
Build me a dynamic SQL query that will search all columns in a particular table for the condition "WHERE column LIKE 'squirrel'"
Assume the database is dbo, and that the table is table_one.
- Assistant
Here's a dynamic SQL query that will search all columns in
dbo.table_onefor the value 'squirrel':DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM dbo.table_one WHERE '; DECLARE @conditions NVARCHAR(MAX) = ''; SELECT @conditions = @conditions + CASE WHEN @conditions = '' THEN '' ELSE ' OR ' END + QUOTENAME(COLUMN_NAME) + ' LIKE ''%squirrel%''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'table_one' AND DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext'); SET @sql = @sql + @conditions; -- Execute the dynamic SQL EXEC sp_executesql @sql;If you want an exact match instead of a partial match, replace
LIKE ''%squirrel%''with= ''squirrel''.Here's an alternative version that prints the generated SQL before executing (useful for debugging):
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM dbo.table_one WHERE '; DECLARE @conditions NVARCHAR(MAX) = ''; SELECT @conditions = @conditions + CASE WHEN @conditions = '' THEN '' ELSE ' OR ' END + QUOTENAME(COLUMN_NAME) + ' LIKE ''%squirrel%''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'table_one' AND DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext'); SET @sql = @sql + @conditions; -- Print the generated SQL PRINT @sql; -- Execute the dynamic SQL EXEC sp_executesql @sql;Note that this query:
- Only searches text-based columns (varchar, nvarchar, char, nchar, text, ntext)
- Uses
%squirrel%for a partial match (contains 'squirrel' anywhere) - Combines all conditions with OR, so it returns rows where ANY column contains 'squirrel'
- Uses
QUOTENAME()to properly escape column names that might contain special characters
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.