Skip to content
TheChatArchiveThe Chat Archive
gpt-4.1-2025-04-14· OpenAIHugging Face LMArena Human Preference 140K

Build me a dynamic SQL query that will search all columns in a particular table for the…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-opus-4-20250514-thinking-16k’s answer.
  1. 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.

  2. Assistant

    Certainly! To search all columns in dbo.table_one for the value 'squirrel' using a dynamic SQL query, you can use the following approach in SQL Server (T-SQL):

    Step 1: Build the Dynamic SQL

    This script will generate and execute a query that checks every column in table_one for the value 'squirrel' using LIKE.

    DECLARE @TableName NVARCHAR(128) = 'table_one';
    DECLARE @SchemaName NVARCHAR(128) = 'dbo';
    DECLARE @SearchValue NVARCHAR(100) = 'squirrel';
    DECLARE @SQL NVARCHAR(MAX) = '';
    
    SELECT @SQL = STRING_AGG(
        QUOTENAME(c.name) + ' LIKE ''%' + @SearchValue + '%''', ' OR ')
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.name = @TableName
      AND s.name = @SchemaName
      AND c.system_type_id IN (167, 175, 231, 239); -- char, varchar, nchar, nvarchar
    
    SET @SQL = 'SELECT * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
               ' WHERE ' + @SQL;
    
    -- Print or execute the dynamic SQL
    PRINT @SQL;
    -- EXEC sp_executesql @SQL;
    

    Notes:

    • This script only checks character columns (char, varchar, nchar, nvarchar). If you want to include other types, adjust the system_type_id filter.
    • The STRING_AGG function is available in SQL Server 2017+. For earlier versions, use FOR XML PATH to concatenate.
    • Uncomment the EXEC sp_executesql @SQL; line to actually run the query.

    If you need this for another RDBMS (like MySQL, PostgreSQL, etc.), let me know!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.