Is it possible that I could find a row contains a string? Assume that I do not know which columns contain a string

Question

I know that there are several ways to find which row's column contains a string, like using [column name] regexp ' ' or [column name] like ' '

while currently what I need some help is I have a table with several columns, all of there are varchar or text and I am not sure which column contains a certain string. Just say that I want to search a "xxx from a table. Several different columns could contain this string or not. Is there a way that I could find which column contains this string?

I have a thinking and the solution could be

 select * from [table name] where [column1] regexp 'xxx' or 
    [column2] regexp 'xxx' or ...... [column39] regexp 'xxx' or .....
[colum60] regexp 'xxx' or ... or [column 80] regexp 'xxx';

I do not want the query like this. Is there another effective way?

To give a better example, say that we are searching for a table that belongs to a blog.

We have title, URL, content, key words, tag, comment and so on. Now we just say, if any blog article is related to "database-normalization", this word may appear in the title, URL or content or anywhere, and I do not want to write it one by one like

where title regexp 'database-normalization' or content regexp 'database-normalization' or url regexp 'database-normalization'......

as when there are hundreds columns, I need to write a hundred, or in this case is there an effective way instead of write hundred or statement? Like using if-else or collections or some others to build the query.

Answer 1

If you want a pure dynamic way, you can try this. I've tried it long back on sql-server and hope it may help you.

#TMP_TABLE -- a temporary  table
- PK, IDENTITY
- TABLE_NAME
- COLUMN_NAME
- IS_EXIST


INSERT INTO #TMP_TABLE (TABLE_NAME,COLUMN_NAME)
SELECT C.TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = <your-table> AND C.DATA_TYPE = 'varchar'; -- you can modify it to handle multiple table at once.

-- boundaries
SET @MINID = (SELECT ISNULL(MIN(<PK>),0) FROM #TMP_TABLE ); 
SET @MAXID = (SELECT ISNULL(MAX(<PK>),0) FROM #TMP_TABLE );

WHILE ((@MINID<=@MAXID) AND (@MINID<>0))
BEGIN
    SELECT @TABLE_NAME = TABLE_NAME,@COLUMN_NAME =  COLUMN_NAME
    FROM  #TMP_TABLE 
    WHERE <PK> = @MINID;

    SET @sqlString =  ' UPDATE #TMP_TABLE 
                    SET IS_EXIST = 1 
                    WHERE EXIST (SELECT 1 FROM '+ @TABLE_NAME+' WHERE  '+ @COLUMN_NAME +' = ''demo.webstater.com'') AND <PK> = '+ @MINID;
    EXEC(@sql) ;
    SET @MINID = (SELECT MIN(<PK>) FROM #TMP_TABLE WHERE <PK> > @MINID );
END 

SELECT * FROM #TMP_TABLE WHERE IS_EXIST = 1 ; -- will give you matched results.

Answer 2

If you know the columns in advance, what you proposed is probably the most effective way (if a little verbose).

Otherwise, you could get the column names from INFORMATION_SCHEMA.COLUMNS and construct dynamic SQL based on that.