27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM TbA WHERE ISNUMERIC(lid)=1 AND(lid BETWEEN 60000 AND 60010 OR lid BETWEEN 90000 AND 99210)
UPDATE TbA SET bt='B' WHERE ISNUMERIC(lid)=1 AND(lid BETWEEN 60000 AND 60010 OR lid BETWEEN 90000 AND 99210)
declare @t Table ( lid varchar(10) not null , bt varchar(10) not null )
insert @t
select '60000', 'A' union all
select '60001', 'A' union all
select '60002', 'B' union all
select '60002' ,'B' union all
select '600ab', 'B' union all
select '99208', 'A' union all
select '99209', 'B' union all
select '9920a', 'A' union all
select '99210', 'A' union all
select '9999', 'B' union all
select 'HA014K1', 'A' union all
select '60010', 'A' union all
select 'HA016K1', 'A'
select * from @t
where (PATINDEX('%[^0-9]%',lid))=0
update @t
set bt='B'
where ( PATINDEX('%[^0-9]%',lid))=0
select * from @t
use TempTest
go
if OBJECT_ID ('Tb1') is not null drop table Tb1
Create Table Tb1 (
colKey varchar(10) not null primary key ,
col1 varchar(10) null,
col2 varchar (10) null
)
Insert into Tb1
Select 'key1' , 'row1' , '2000' union all
Select 'key2' , 'row2' , '2001' union all
Select 'key3' , 'row3' , '2a02' union all
Select 'key4' , 'row4' , '2003' union all
Select 'key5' , 'row5' , '200b' union all
Select 'key6' , 'row6' , '90000' union all
Select 'key7' , 'row7' , '900aa' union all
Select 'key8' , 'row8' , '9b002' union all
Select 'key9' , 'row9' , '90003' union all
Select 'key10' , 'row10' , '90004'
SELECT [colKey],[col1],[col2] FROM [TempTest].[dbo].[Tb1]
------要求查出col2 2000-2003 或者90000 - 90004 的值的行
SELECT * FROM Tb1 WHERE ISNUMERIC(col2)=1 AND(col2 BETWEEN 2000 AND 2003 OR col2 BETWEEN 90000 AND 90004) order by col2
-------将col2 2000-2003 或者90000 - 90004 的值的行的COL1 的值设置为N
UPDATE Tb1 SET col1='N' WHERE ISNUMERIC(col2)=1 AND(col2 BETWEEN 2000 AND 2003 OR col2 BETWEEN 90000 AND 90004)
--------------开始查询--------------------------
SELECT * FROM (select CAST(LID AS INT ) lid from [tb]
WHERE ISNUMERIC(lid)=1 ) A
WHERE (A.LID BETWEEN 60000 AND 60010 ) OR (A.LID BETWEEN 90000 AND 99210)
----------------结果----------------------------
/*
之前有个大牛是这样查的, 但是我用做更新时还是出问题. 好像用引号在有时候不行.
--1.查询出 lid 为 60000-60010 或者 90000-99209 之间的记录(都为数字).
SELECT * FROM TableA WHERE (LID BETWEEN '60000' AND '60010' OR LID BETWEEN '90000' AND '99209') AND LID NOT LIKE '%[^0-9]%'
--2. 更新 60000-60010 或者 90000-99209 字段 BT 值为 B
UPDATE TableA SET BT='B'WHERE (LID BETWEEN '60000' AND '60010' OR LID BETWEEN '90000' AND '99209') AND LID NOT LIKE '%[^0-9]%'
--1.查询出 lid 为 60000-60010 或者 90000-99209 之间的记录(都为数字).
SELECT * FROM TableA WHERE LID BETWEEN '60000' AND '60010' OR LID BETWEEN '90000' AND '99209' AND LID NOT LIKE '%[^0-9]%'
--2. 更新 60000-60010 或者 90000-99209 字段 BT 值为 B
UPDATE TableA SET BT='B'WHERE LID BETWEEN '60000' AND '60010' OR LID BETWEEN '90000' AND '99209' AND LID NOT LIKE '[^0-9]'
--1.查询出 lid 为 60000-60010 或者 90000-99209 之间的记录(都为数字).
SELECT * FROM TableA WHERE LID BETWEEN '60000' AND '60010' OR LID BETWEEN '90000' AND '99209' AND LID NOT LIKE '[^0-9]'
--2. 更新 60000-60010 或者 90000-99209 字段 BT 值为 B
SELECT * FROM TableA WHERE LID BETWEEN '60000' AND '60010' OR LID BETWEEN '90000' AND '99209' AND LID NOT LIKE '[^0-9]' AND BT='B'