sql 查询中 怎么判断一个 字段中的 内容中包括 我提供字符串的每一个字符
例如：
表tb1中 fa 字段 3条记录
id fa
1 abc123
2 ac1234
3 bc2345
我如何选出 fa 字段中包含字符串“ac2”这3个字符的记录。
------解决方案--------------------------------------------------------
select * from tb1 where fa like '%ac2%'
------解决方案--------------------------------------------------------
select * from tb1 where fa like '%a%c%2%'
------解决方案--------------------------------------------------------
好像要写一个函数
------解决方案--------------------------------------------------------
select * from tb1 where fa like '%a%' and fa like'%c%'and fa like'%2%'
------解决方案--------------------------------------------------------
- SQL code
select * from tb1 where fa like '%a%' or fa like'%c%'or fa like'%2%'
------解决方案--------------------------------------------------------
上面理解错了
select * from tb1
where patindex('%a%',fa)>0
and patindex('%b%',fa)>0
patindex('%c%',fa)>0
------解决方案--------------------------------------------------------
- SQL code
declare @tb table (id int,fa varchar(10))insert into @tb select 1,'abc123'insert into @tb select 2,'ac1234'insert into @tb select 3,'bc2345'select * from @tb where charindex('a',fa)>0 and charindex('c',fa)>0 and charindex('2',fa)>0
------解决方案--------------------------------------------------------
以前遇到过这个问题,是用函数实现的,现在不记得怎么写了.
------解决方案--------------------------------------------------------
id fa
1 abc123
2 ac1234
3 bc2345
id 为 3 是不是你想要查的,如果是 用 or 不是 用 and
------解决方案--------------------------------------------------------
- C# code
string whereStr = "select * from tb1 where 1=1 "; string tempStr = "ac2"; foreach (char c in tempStr) { whereStr += " and charindex('" + c + "',fa)> 0 "; }
------解决方案--------------------------------------------------------
可以试试启用sqlserver的全文索引
sp_fulltext_database 'enable'
execute sp_fulltext_catalog 'ft_test', 'create'
execute sp_fulltext_table 'tb1','create', 'ft_test','PK_tb1'
execute sp_fulltext_column 'tb1', 'fa', 'add'
execute sp_fulltext_catalog 'ft_test', 'start_full'
select * from tb1
where freetext(*,'keyword1 keyword2')
------解决方案--------------------------------------------------------
- SQL code
create table tb (id int,fa varchar(10))insert into tb select 1,'abc123'insert into tb select 2,'ac1234'insert into tb select 3,'bc2345'declare @s varchar(50),@where varchar(8000)select @s='ac2',@where ='where 1=1'while(len(@s)>0)beginset @where =@where+' and charindex('''+left(@s,1)+''',fa)>0 'set @s=right(@s,len(@s)-1)endexec('select * from tb '+@where)
------解决方案--------------------------------------------------------
- C# code
public string SQLStr(string searchCondition){ StringBuilder sql=new StringBuilder("select * form tb1 where 1=1 "); for(int i=0;i<searchCondition;i++) { sql.Append(" and fa like '%"+searchCondition[i]+"%'"); } return sql.ToString();}
------解决方案--------------------------------------------------------