*不是通配符吧 查询 select * from tb where colname like '%*%'
select * from @tb where charindex('*',status)>0
--> 测试数据: @tb declare @tb table (status varchar(10)) insert into @tb select '1**00' union all select 'g_d' union all select '*' union all select '' select * from @tb where charindex('*',status)>0 or charindex('_',status)>0status ---------- 1**00 g_d *(3 行受影响)
A. 在 WHERE 中使用 LIKE 和 ESCAPE 语法 下例假定 description 列存在于 finances 表内。若要搜索其中的 description 列包含精确字符 g_ 的行,请使用 ESCAPE 选项,因为 _ 是通配符。如果不指定 ESCAPE 选项,查询将搜索任何包含字母 g 后跟除 _ 字符外的任何单个字符的描述值。SELECT * FROM finances WHERE description LIKE 'gs_' ESCAPE 'S'
if object_id('tb') is not null drop table tb go create table tb(colName nvarchar(20)) goinsert into tb values('abf') insert into tb values('abcd*ef')select * from tb where colname like '%*%' /* colName -------------------- abcd*ef(所影响的行数为 1 行)*/
--> 测试数据: @tb declare @tb table (status varchar(10)) insert into @tb select '1**00' union all select 'g_d' union all select '*' union all select '' select * from @tb where status like '%[*_]%' status ---------- 1**00 g_d *(3 行受影响)
declare @tb table (status varchar(10)) insert into @tb select '1**00' union all select 'g_d' union all select '*' union all select '' SELECT * FROM @TB WHERE STATUS LIKE '%S*%' ESCAPE 'S'(所影响的行数为 4 行)status ---------- 1**00 *(所影响的行数为 2 行)
if object_id('tb') is not null drop table tb go create table tb(colName nvarchar(20)) goinsert into tb values('abf') insert into tb values('abcd*ef') insert into tb values('abcd%ef') insert into tb values('abcd_ef')select * from tb where colname like '%*%' /* colName -------------------- abcd*ef(所影响的行数为 1 行)*/select * from tb where colname like '%[%]%' /* colName -------------------- abcd%ef(所影响的行数为 1 行)*/ select * from tb where colname like '%[_]%' /* colName -------------------- abcd_ef(所影响的行数为 1 行)*/
字段id ksdlkfjslkdfj_*001 sldkfjslkfjjl_*002 sldkfslkfjsld_*003 ....类似于这样的数据,我想检索 id为 sldkfjslkfjjl_*002 的记录,百万级的数据量进行检索下划线 和 星号 不作为通配符 处理,考虑效率,sql语句改怎么写呢? where id like sdlfkjsdlkf[_][*]001, 这样*号还是作为通配符处理的吧?
有什么问题吗?--> 测试数据: @tb declare @tb table (status varchar(100)) insert into @tb select 'sldkfjslkfjjl_*002' union all select 'sldkfslkfjsld_*003' union all select 'ksdlkfjslkdfj_*001' union all select '' select * from @tb where status like '%[*_]%' status ---------------------------------------------------------------------------------------------------- sldkfjslkfjjl_*002 sldkfslkfjsld_*003 ksdlkfjslkdfj_*001(3 行受影响)
create table #tb(colName nvarchar(20)) goinsert into #tb values('abf') insert into #tb values('abcd*ef') insert into #tb values('abcd%ef') insert into #tb values('abcd_ef') insert into #tb values('*abcd_ef') insert into #tb values('*abcd_ef*')select * from #tb where colName like '%[*]%'colName -------------------- abcd*ef *abcd_ef *abcd_ef*
或者 这样写 where id like 'sdlfkjsdlkf[_*]001' ??? where id = 'sdlfkjsdlkf[_*]001' ???
select * from tb where charindex('*',字段)>0
select * from dbo.A where stuName like '%1*%' ESCAPE '1'
查询
select * from tb where colname like '%*%'
where charindex('*',status)>0
--> 测试数据: @tb
declare @tb table (status varchar(10))
insert into @tb
select '1**00' union all
select 'g_d' union all
select '*' union all
select '' select * from @tb
where charindex('*',status)>0 or charindex('_',status)>0status
----------
1**00
g_d
*(3 行受影响)
下例假定 description 列存在于 finances 表内。若要搜索其中的 description 列包含精确字符 g_ 的行,请使用 ESCAPE 选项,因为 _ 是通配符。如果不指定 ESCAPE 选项,查询将搜索任何包含字母 g 后跟除 _ 字符外的任何单个字符的描述值。SELECT *
FROM finances
WHERE description LIKE 'gs_' ESCAPE 'S'
if object_id('tb') is not null
drop table tb
go
create table tb(colName nvarchar(20))
goinsert into tb values('abf')
insert into tb values('abcd*ef')select * from tb where colname like '%*%'
/*
colName
--------------------
abcd*ef(所影响的行数为 1 行)*/
--> 测试数据: @tb
declare @tb table (status varchar(10))
insert into @tb
select '1**00' union all
select 'g_d' union all
select '*' union all
select '' select * from @tb
where status like '%[*_]%'
status
----------
1**00
g_d
*(3 行受影响)
insert into @tb
select '1**00' union all
select 'g_d' union all
select '*' union all
select '' SELECT * FROM @TB WHERE STATUS LIKE '%S*%' ESCAPE 'S'(所影响的行数为 4 行)status
----------
1**00
*(所影响的行数为 2 行)
drop table tb
go
create table tb(colName nvarchar(20))
goinsert into tb values('abf')
insert into tb values('abcd*ef')
insert into tb values('abcd%ef')
insert into tb values('abcd_ef')select * from tb where colname like '%*%'
/*
colName
--------------------
abcd*ef(所影响的行数为 1 行)*/select * from tb where colname like '%[%]%'
/*
colName
--------------------
abcd%ef(所影响的行数为 1 行)*/
select * from tb where colname like '%[_]%'
/*
colName
--------------------
abcd_ef(所影响的行数为 1 行)*/
ksdlkfjslkdfj_*001
sldkfjslkfjjl_*002
sldkfslkfjsld_*003
....类似于这样的数据,我想检索 id为 sldkfjslkfjjl_*002 的记录,百万级的数据量进行检索下划线 和 星号 不作为通配符 处理,考虑效率,sql语句改怎么写呢?
where id like sdlfkjsdlkf[_][*]001, 这样*号还是作为通配符处理的吧?
有什么问题吗?--> 测试数据: @tb
declare @tb table (status varchar(100))
insert into @tb
select 'sldkfjslkfjjl_*002' union all
select 'sldkfslkfjsld_*003' union all
select 'ksdlkfjslkdfj_*001' union all
select '' select * from @tb
where status like '%[*_]%'
status
----------------------------------------------------------------------------------------------------
sldkfjslkfjjl_*002
sldkfslkfjsld_*003
ksdlkfjslkdfj_*001(3 行受影响)
create table #tb(colName nvarchar(20))
goinsert into #tb values('abf')
insert into #tb values('abcd*ef')
insert into #tb values('abcd%ef')
insert into #tb values('abcd_ef')
insert into #tb values('*abcd_ef')
insert into #tb values('*abcd_ef*')select * from #tb where colName like '%[*]%'colName
--------------------
abcd*ef
*abcd_ef
*abcd_ef*
where id = 'sdlfkjsdlkf[_*]001' ???
select * from dbo.A
where stuName like '%1*%' ESCAPE '1'
LIKE '5[%]' 5%
LIKE '5%' 5 后跟 0 个或多个字符的字符串
LIKE '[_]n' _n
LIKE '_n' an, in, on (and so on)
LIKE '[a-cdf]' a、b、c、d 或 f
LIKE '[-acdf]' -、a、c、d 或 f
LIKE '[ [ ]' [
LIKE ']' ]