27,579
社区成员
发帖
与我相关
我的任务
分享
create table test(t1 varchar(20),t2 varchar(20),t3 varchar(20))
insert into test values('0.25mg', null ,null)
insert into test values('0.5mg' , null ,null)
insert into test values('1g' , null ,null)
insert into test values('500ml' , null ,null)
insert into test values('60粒' , null ,null)
go
--查询
select t1,
case when PATINDEX('%[吖-做]%',t1) > 1 then left(t1,PATINDEX('%[吖-做]%',t1)-1)
when PATINDEX('%[a-z]%',t1) > 1 then left(t1,PATINDEX('%[a-z]%',t1)-1)
else t1 end t2,
case when PATINDEX('%[吖-做]%',t1) > 1 then substring(t1,PATINDEX('%[吖-做]%',t1),len(t1))
when PATINDEX('%[a-z]%',t1) > 1 then substring(t1,PATINDEX('%[a-z]%',t1),len(t1))
else t1 end t3
from test
--更新
update test
set t2 = (case when PATINDEX('%[吖-做]%',t1) > 1 then left(t1,PATINDEX('%[吖-做]%',t1)-1)
when PATINDEX('%[a-z]%',t1) > 1 then left(t1,PATINDEX('%[a-z]%',t1)-1)
else t1 end),
t3 = (case when PATINDEX('%[吖-做]%',t1) > 1 then substring(t1,PATINDEX('%[吖-做]%',t1),len(t1))
when PATINDEX('%[a-z]%',t1) > 1 then substring(t1,PATINDEX('%[a-z]%',t1),len(t1))
else t1 end)
select * from test
drop table test
/*
t1 t2 t3
-------------------- -------------------- --------------------
0.25mg 0.25 mg
0.5mg 0.5 mg
1g 1 g
500ml 500 ml
60粒 60 粒
(所影响的行数为 5 行)
*/
use Tempdb
go
--> -->
if not object_id(N'test') is null
drop table test
Go
Create table test([t1] nvarchar(10),[t2] nvarchar(10),[t3] nvarchar(10))
Insert test
select N'0.25mg',null,null union all
select N'0.5mg',null,null union all
select N'1g',null,null union all
select N'500ml',null,null union all
select N'60粒',null,null
Go
UPDATE Test
SET [t2]=LEFT([t1],PATINDEX('%[^0-9.]%',[t1])-1),[t3]=RIGHT([t1],PATINDEX('%[0-9.]%',REVERSE([t1]))-1)
go
SELECT * FROM Test
/*
t1 t2 t3
0.25mg 0.25 mg
0.5mg 0.5 mg
1g 1 g
500ml 500 ml
60粒 60 粒
*/
create table tb(area varchar(20))
insert into tb values('100平方米')
insert into tb values('120平方米')
insert into tb values('70-80平方米')
go
--方法一
[吖-咗]
select left(area,PATINDEX('%[吖-做]%',area)-1) area from tb
/*
area
--------------------
100
120
70-80
(所影响的行数为 3 行)
*/
--方法二
--建立如下函数(非a-z A-Z 0-9 - , 以及中文字符之外的字符删除)
go
create function getnewstr(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^a-z,A-Z,0-9,-]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
select area = dbo.getnewstr(area) from tb where area like('%[^a-z,A-Z,0-9,-]%')
/*
area
--------------------
100
120
70-80
(所影响的行数为 3 行)
*/
drop table tb
drop function dbo.getnewstr