我有可能找到一个包含字符串的行吗？假设我不知道哪个列包含

This is the last edition. I have tried to express clearly.

这是最后一版。我试图表达清楚。

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

我知道有几种方法可以找到哪个行的列包含字符串，例如使用[column name] regexp''或[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?

虽然目前我需要一些帮助是我有一个包含多个列的表，所有都有varchar或文本，我不确定哪个列包含某个字符串。只是说我想从表中搜索“xxx。几个不同的列可以包含这个字符串。有没有办法可以找到哪个列包含这个字符串？

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

因为当有数百列时，我需要写一百或......在这种情况下是否有一种有效的方式而不是写百或语句......？就像使用if-else或集合或其他一些来构建查询一样...

Thank you.

谢谢。

6 个解决方案

#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.

如果你想要一个纯粹的动态方式，你可以试试这个。我已经在sql-server上做了很久，并希望它可以帮到你。

#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.

#2

His question is not to query specific columns with like clause. He has been asking to apply same pattern across columns dynamically.

他的问题不是查询具有like子句的特定列。他一直要求动态地在列之间应用相同的模式。

Example: Table having 3 columns - FirstName, LastName, Address and pattern matching is "starts with A" then resulting query should be: Select * From Customer where FirstName like 'A%" or LastName like 'A%" or Address like 'A%'

示例：具有3列的表 - FirstName，LastName，Address和模式匹配是“以A开头”，然后生成的查询应为：选择* From Customer，其中FirstName喜欢'A％'或LastName喜欢'A％'或Address喜欢'A ％”

If you want to build query in business layer, this could easily be done with reflection along with EF.

如果要在业务层中构建查询，可以通过反射和EF轻松完成。

If you are motivated to do in database then you can achieve by building query dynamically and then execute through sp_executesql.

如果您有动力在数据库中执行，那么您可以通过动态构建查询然后通过sp_executesql执行来实现。

#3

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.

否则，您可以从INFORMATION_SCHEMA.COLUMNS获取列名，并基于此构造动态SQL。

#4

Try this (Just pass tablename and the string to be find)-:

试试这个（只需传递tablename和要查找的字符串） - ：

create proc usp_findString 
@tablename varchar(500),
@string varchar(max)
as
Begin

Declare @sql2 varchar(max),@sql nvarchar(max)
SELECT @sql2=
STUFF((SELECT ', case when '+QUOTENAME(NAME)+'='''+@string+''' then 1 else 0 end as '+NAME
FROM (select a.name from sys.columns a join sys.tables b on a.[object_id]=b.[object_id] where b.name=@tablename) T1
--WHERE T1.ID=T2.ID
FOR XML PATH('')),1,1,'')
--print @string
set @sql='select '+@sql2+' from '+@tablename
print @sql
EXEC sp_executesql @sql

End

SQL Server 2014

#5

One way is to use CASE to check the substring existence with LOCATE in mysql and return the column but all you have to check in every column of the table as below:

一种方法是使用CASE在mysql中使用LOCATE检查子字符串的存在并返回列，但是您必须检查表的每一列，如下所示：

CREATE TABLE test(col1 VARCHAR(1000), col2 VARCHAR(1000), col3 VARCHAR(1000))
INSERT INTO test VALUES
('while currently what I need some help is I have a table with 10 columns',
'contains a certain string. Just say that I want to search a table',
'contains a certain string demo.webstater.com')

SELECT (CASE WHEN LOCATE('demo.webstater.com', col1, 1) > 0 THEN 'col1'
        WHEN LOCATE('demo.webstater.com', col2, 1) > 0 THEN 'col2'
        WHEN LOCATE('demo.webstater.com', col3, 1) > 0 THEN 'col3'  
    END) whichColumn
FROM test

OUTPUT:

OUTPUT：

whichColumn
col3

#6

-2

There are many ways in which you can do your analysis. You can use "LIKE A%%" if it starts from A in SQL, "REGEX" LibrarY for multiple checks.

您可以通过多种方式进行分析。如果它从SQL中的A开始，则可以使用“LIKE A %%”，“REGEX”LibrarY用于多次检查。

#1