比如 t1 表有这样的一个字段 c , c 字段中有这样的值,每行一个。
-------------------------------------------------------------
aaa
bcds
a1
啊
^%
ew1
344
__
213_21
a_2
d
ddd
电风扇
-------------------------------------------------------------
如何搜索出下面这些字段呢?
啊
^%
电风扇
-------------------------------------------------------------
aaa
bcds
a1
啊
^%
ew1
344
__
213_21
a_2
d
ddd
电风扇
-------------------------------------------------------------
如何搜索出下面这些字段呢?
啊
^%
电风扇
where patindex('%[a-z]%',c)=0 and patindex('%[0-9]%',c)=0
and charindex('_',c)=0
if object_id('[t1]') is not null drop table [t1]
create table [t1]([c] varchar(6))
insert [t1]
select 'aaa' union all
select 'bcds' union all
select 'a1' union all
select '啊' union all
select '^%' union all
select 'ew1' union all
select '344' union all
select '__' union all
select '213_21' union all
select 'a_2' union all
select 'd' union all
select 'ddd' union all
select '电风扇'select * from [t1]
where patindex('%[a-z,0-9]%',c)=0
and charindex('_',c)=0----------------------
啊
^%
电风扇
select * from [t1] WHERE PATINDEX('%[^0-9a-z_]%',c)=1
/**
c
------
啊
^%
电风扇(所影响的行数为 3 行)
**/
create table [t1]([c] varchar(6))
insert [t1]
select 'aaa' union all
select 'bcds' union all
select 'a1' union all
select '啊' union all
select '^%' union all
select 'ew1' union all
select '344' union all
select '__' union all
select '213_21' union all
select 'a_2' union all
select 'd' union all
select 'ddd' union all
select '电风扇'select * from [t1] where patindex('%[a-z,0-9,_]%',c)=0 drop table t1/*
c
------
啊
^%
电风扇(所影响的行数为 3 行)*/
and datalength(c)<>len(c)*2
-------
^%
and datalength(c)=len(c)
and datalength(c)=len(c)
这个问题有个延伸的问题,在下面的帖子,希望大家接着帮忙解决,谢谢:http://topic.csdn.net/u/20100111/15/c2a124c5-bc5b-4626-86ce-c5b862cf5cff.html这个帖子就结贴了。
select * from [t1] WHERE PATINDEX('%[0-9a-z_吖-座]%',c)=0