数据库存在[SNO]一列,为varchar型.如下所示 sno name
56 aaa
056 nnn
0056 acs
2056 ad
F0056 fff
10056 sss
S56
请问: 怎么用一句SQL查出 所有以0开始 , 尾数是56的记录,包括56本身也要查出来 (56前面只能是0)
56 aaa
056 nnn
0056 acs
2056 ad
F0056 fff
10056 sss
S56
请问: 怎么用一句SQL查出 所有以0开始 , 尾数是56的记录,包括56本身也要查出来 (56前面只能是0)
where sno like '0%56'
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([sno] varchar(5),[name] varchar(3))
insert [TB]
select '56','aaa' union all
select '056','nnn' union all
select '0056','acs' union all
select '2056','ad' union all
select 'F0056','fff' union all
select '10056','sss' union all
select 'S56',null
GO--> 查询结果
SELECT * FROM [TB] where sno like '%[0]56'
--> 删除表格
--DROP TABLE [TB]
0056 aaa
056 ccc
56 ccc
B56 aaa
B056 fff
5056 aaa
000056 aaa-----------------------------------
我不确定SNO的位数, 要查出的结果为
sno name
0056 aaa
056 ccc
56 ccc
000056 aaa
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([sno] varchar(6),[name] varchar(3))
insert [TB]
select '0056','aaa' union all
select '056','ccc' union all
select '56','ccc' union all
select 'B56','aaa' union all
select 'B056','fff' union all
select '5056','aaa' union all
select '000056','aaa'
GO--> 查询结果
SELECT * FROM [TB] where sno like '[0,5]%56'
--> 删除表格
--DROP TABLE [TB]
insert @a select '56', 'aaa'
UNION ALL SELECT '056', 'nnn'
UNION ALL SELECT '0056', 'acs'
UNION ALL SELECT '2056', 'ad'
UNION ALL SELECT 'F0056', 'fff'
UNION ALL SELECT '10056', 'sss'
UNION ALL SELECT 'S56',NULL
SELECT sno FROM @a
WHERE RIGHT(sno,2)='56' AND REPLACE(sno,'0','')='56'--result
/*
sno
--------------------
56
056
0056(所影响的行数为 3 行)
*/
drop table lina
go
create table lina(sno varchar(100),name varchar(100))
insert into lina select '56','aaa'
insert into lina select '056','nnn'
insert into lina select '0056','acs'
insert into lina select '2056','ad'
insert into lina select 'f0056','fff'
insert into lina select '10056','sss'
go
select * from lina where sno like'0%' and sno like '%56'
insert [#TB]
select '0056','aaa' union all
select '056','ccc' union all
select '56','ccc' union all
select 'B56','aaa' union all
select 'B056','fff' union all
select '5056','aaa' union all
select '000056','aaa'
GOselect * from #tb
where left(sno,1)='0' and
cast(substring(sno,1,charindex('56',sno)+1) as int)=56 --或者加right(sno,2)='56'
union
select * from #tb where sno='56'
order by sno descsno name
------ ----
56 ccc
056 ccc
0056 aaa
000056 aaa(4 行受影响)
SQL就变成 SNO=56了 56后面的0也被替换为空了 实际要查的是SNO=560
还是不行哦
select * from #tb
where left(sno,1)='0' and
cast(substring(sno,1,charindex('56',sno)+1) as int)=56 and right(sno,2)='56'
union
select * from #tb where sno='56'
order by sno desc
00560
056001
000056
110506
56
056
如果在键盘输入 56 的话查出的记录要显示
000056
56
056
也就是56为右边最后两位,56的前面可以有很多位,但是只能是0补充的。有没有高手在线等哦。
当SNO等于 056156 的时候 这个SQL也会查出来的
SELECT * FROM [TB] where left(sno,1)='0' and right(sno,2)='56'
这个测过,lz有问题再找我。
where left(sno,1)='0' and
patindex('%[A-Za-z]%',sno)=0 and --查不到字母
replace(sno,'0','')='56' --或者cast(sno as int)=56
and right(sno,2)='56'
union
select * from #tb where sno='56'
order by sno desc
insert @a select '56', 'aaa'
UNION ALL SELECT '056', 'nnn'
UNION ALL SELECT '0056', 'acs'
UNION ALL SELECT '2056', 'ad'
UNION ALL SELECT 'F0056', 'fff'
UNION ALL SELECT '10056', 'sss'
UNION ALL SELECT 'S56',NULL
SELECT sno FROM @a
WHERE RIGHT(sno,2)='56' AND REPLACE(sno,'0','')='56'7#楼正解