sqlite搜索多列 [英] sqlite search multiple column

问题描述

我试图对表中的所有列执行区分大小写的搜索，所以我做了这样的事情

am trying to perform a case sensitive serach of all columns in a table, so i did somthing like this

Select * From mytable Where col1 ||'--' ||col2 ||'--' ||诸如 '%SomeValue%' 之类的

但是对于大写和小写它总是返回相同的结果.如果我这样做

but it alwas return the same result for both upper case and lower case. if i do this

Select * From mytable Where col1 like '%SomeValue%' OR col1 like '%SomeValue%' etc

我得到了想要的结果.这里的问题是我不能使用第二个查询，因为我有大约 36 列要搜索，并且将 col1 like '%SomeValue%' 写入 36 次是不必要的.

i get the desired result. The problems here is that i cannot use this second query as i have about 36 columns to search, and writing col1 like '%SomeValue%' up to 36 times would be unecessary.

有没有人有解决办法?

推荐答案

一种解决方案是使用 glob 而不是 like

One solution is to use glob instead of like

sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') like '%Bar%';
table|t|t|2|CREATE TABLE t (a)
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') glob '*Bar*';
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') glob '*bar*';
table|t|t|2|CREATE TABLE t (a)
sqlite> 

另一种解决方案是使用 pragma case_sensitive_like

Another solution is to use pragma case_sensitive_like

sqlite> PRAGMA case_sensitive_like = 1;
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') like '%Bar%';
sqlite> select * from sqlite_master where ('foo' || '--' || 'bar') like '%bar%';
table|t|t|2|CREATE TABLE t (a)
sqlite> 

这篇关于sqlite搜索多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！