According to your comments, you want to delete all rows matching this string in all such columns in the database. So just build up a string of all that using STRING_AGG and the system tables.

DECLARE @search varchar(1000) = 'YourSearchTerm';

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG('
DELETE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
WHERE ' + c.whereClause + ';
', '')
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
CROSS APPLY (
    SELECT whereClause = CAST(STRING_AGG(QUOTENAME(c.name) + ' = @search', ' OR ') AS nvarchar(max))
    FROM sys.columns c
    WHERE c.object_id = t.object_id
      AND c.name LIKE 'CNUM%'
    GROUP BY ()   -- need this in order to exclude tables with 0 columns
) c;

PRINT @sql; -- your friend

-- EXEC sp_executesql @sql,
--   N'@search varchar(1000)',
--   @search = @search;

-- uncomment when you got the right commands

If the data types or lengths are different then you might want a cast as well.

If you want this to work on all databases then you need dynamic-over-dynamic.

Place all the above code into a @sql variable, escaping all ' with '' . Then run a cursor over all databases, executing for each one EXEC database.sys.sp_executesql @sql

DECLARE @sql nvarchar(max) = '
DECLARE @search varchar(1000) = ''YourSearchTerm'';

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(''
DELETE '' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''
WHERE '' + c.whereClause + '';
'', '''')
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
CROSS APPLY (
    SELECT whereClause = CAST(STRING_AGG(QUOTENAME(c.name) + '' = @search'', '' OR '') AS nvarchar(max))
    FROM sys.columns c
    WHERE c.object_id = t.object_id
      AND c.name LIKE ''CNUM%''
    GROUP BY ()   -- need this in order to exclude tables with 0 columns
) c;

PRINT @sql; -- your friend

EXEC sp_executesql @sql,
  N''@search varchar(1000)'',
  @search = @search;
';

DECLARE @proc nvarchar(1000);
DECLARE @crsr CURSOR;
SET @crsr = CURSOR FORWARD_ONLY STATIC FOR
    SELECT QUOTENAME(name) + '.sys.sp_executesql'
    FROM sys.databases
    WHERE database_id > 4;  -- not system databases
OPEN @crsr;
WHILE 1=1
BEGIN
    FETCH NEXT FROM @crsr INTO @proc;
    IF @@FETCH_STATUS <> 0
        BREAK;

    EXEC @proc @sql;
END;

You can use dynamic SQL in this case if you want to search for or delete a specific string in a specified column across all tables in all databases on your server.

CREATE PROCEDURE SearchAndDeleteString
    @searchString VARCHAR(255),
    @displayTable BIT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @databaseName VARCHAR(255);
    DECLARE @tableName VARCHAR(255);
    DECLARE @columnName VARCHAR(255);
    DECLARE @sql NVARCHAR(MAX);
    
    -- Create a temporary table to store the results if @displayTable is 1
    IF @displayTable = 1
    BEGIN
        CREATE TABLE #SearchResult (
            [database] VARCHAR(255),
            [table] VARCHAR(255),
            [column] VARCHAR(255)
        );
    END
    
    -- Cursor to iterate through all databases
    DECLARE databaseCursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE state = 0 AND name NOT IN ('master', 'tempdb', 'model', 'msdb');
    
    OPEN databaseCursor;
    FETCH NEXT FROM databaseCursor INTO @databaseName;
    
    -- Iterate through all databases
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Cursor to iterate through all tables in the current database
        DECLARE tableCursor CURSOR FOR
        SELECT TABLE_SCHEMA + '.' + TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME LIKE 'CNUM%';
        
        -- Execute the following code for each table in the current database
        SET @sql = N'';
        OPEN tableCursor;
        FETCH NEXT FROM tableCursor INTO @tableName;
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Cursor to iterate through all columns in the current table
            DECLARE columnCursor CURSOR FOR
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA + '.' + TABLE_NAME = @tableName
                AND COLUMN_NAME LIKE 'CNUM%';
            
            -- Execute the following code for each column in the current table
            OPEN columnCursor;
            FETCH NEXT FROM columnCursor INTO @columnName;
            
            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- Search for or delete the specified string in the current column
                IF @displayTable = 1
                BEGIN
                    SET @sql += N'SELECT ''' + @databaseName + ''', ''' + @tableName + ''', ''' + @columnName + '''
                    WHERE EXISTS (
                        SELECT 1
                        FROM ' + @tableName + '
                        WHERE ' + @columnName + ' = ''' + @searchString + '''
                    ); ';
                END
                ELSE
                BEGIN
                    SET @sql += N'UPDATE ' + @tableName + '
                    SET ' + @columnName + ' = REPLACE(' + @columnName + ', ''' + @searchString + ''', '''');
                    WHERE ' + @columnName + ' LIKE ''%' + @searchString + '%''; ';
                END
                
                FETCH NEXT FROM columnCursor INTO @columnName;
            END
            
            CLOSE columnCursor;
            DEALLOCATE columnCursor;
            
            FETCH NEXT FROM tableCursor INTO @tableName;
        END
        
        CLOSE tableCursor;
        DEALLOCATE tableCursor;
        
        -- Execute the dynamic SQL for the current database
        IF @displayTable = 1
        BEGIN
            EXEC sp_executesql @sql;
        END
        ELSE
        BEGIN
            EXEC (@sql);
        END
        
        FETCH NEXT FROM databaseCursor INTO @databaseName;
    END
    
    CLOSE databaseCursor;
    DEALLOCATE databaseCursor;