34,838
社区成员




create table tb(col char(20))
insert into tb values('照片21cm')
insert into tb values('彩图12cm')
insert into tb values('有光盘')
insert into tb values('21cm')
insert into tb values(null)
go
create function f_get_c(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^吖-咗]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
create function f_get_e(@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 dbo.f_get_c(col) col_c , dbo.f_get_e(col) col_e from tb where col like('%[^吖-咗]%') or col like('%[^a-z,A-Z,0-9]%')
union all
select col1 = null,col2 = null from tb where col is null
drop table tb
drop function f_get_e
drop function f_get_c
/*
col_c col_e
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
照片 21cm
彩图 12cm
有光盘
21cm
NULL NULL
(所影响的行数为 5 行)
*/
create table tb(col char(20))
insert into tb values('照片21cm')
insert into tb values('彩图12cm')
insert into tb values('有光盘')
insert into tb values('21cm')
insert into tb values(null)
go
create function f_get_c(@oldstr varchar(100)) returns varchar(100)
as
begin
declare @i int
set @i = 1
while @i <= len(@oldstr)
if substring(@oldstr, @i, 1) like('[^吖-咗]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
go
create function f_get_e(@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 id = identity(int , 1 , 1) , dbo.f_get_c(col) col_c into tmp1 from tb where col like('%[^吖-咗]%')
select id = identity(int , 1 , 1) , dbo.f_get_e(col) col_e into tmp2 from tb where col like('%[^a-z,A-Z,0-9]%')
select m.col_c , n.col_e from tmp1 m , tmp2 n where m.id = n.id
union all
select col1 = null,col2 = null from tb where col is null
drop table tb , tmp1 , tmp2
drop function f_get_e
drop function f_get_c
/*
col_c col_e
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
照片 21cm
彩图 12cm
有光盘
21cm
NULL NULL
(所影响的行数为 5 行)
*/
create table tb(col char(20))
insert into tb values('照片21cm')
insert into tb values('彩图12cm')
insert into tb values('有光盘')
insert into tb values('21cm')
insert into tb values(null)
go
select col1=left(col,patindex('%[^吖-咗]%',col)-1),
col2=substring(col,patindex('%[^吖-咗]%',col),len(col))
from tb
where patindex('%[^吖-咗]%',col) > 0
union all
select col1 = null,col2 = null from tb where col is null
drop table tb
/*
col1 col2
-------------------- --------------------
照片 21cm
彩图 12cm
有光盘
21cm
NULL NULL
(所影响的行数为 5 行)
*/