22,199
社区成员
发帖
与我相关
我的任务
分享
create table tb(col varchar(20))
insert into tb values('Aabc1')
insert into tb values('5Dsc4')
insert into tb values('C6-hk')
insert into tb values('sd+9t')
insert into tb values('hsd(5]')
insert into tb values('号D.9')
insert into tb values('5T中e')
go
--建立如下函数(非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 m.* from tb m where not exists(select 1 from
(select col from tb where col like('%[^a-z,A-Z,0-9,.,-]%')) n where m.col = n.col)
drop table tb
drop function dbo.getnewstr
/*
col
--------------------
Aabc1
5Dsc4
C6-hk
(所影响的行数为 3 行)
*/
create table tb(col char(6))
insert into tb select 'Aabc1'
insert into tb select '5Dsc4'
insert into tb select 'C6-hk'
insert into tb select 'sd+9t'
insert into tb select 'hsd(5]'
insert into tb select '号D.9'
insert into tb select '5T中e'
go
select * from tb where col like '[a-z,0-9,-][a-z,0-9,-][a-z,0-9,-][a-z,0-9,-][a-z,0-9,-]'
/*
col
----------
Aabc1
5Dsc4
C6-hk
(所影响的行数为 3 行)
*/
go
drop table tb
select * from [tb] where exists(select 1 from tb where PATINDEX('%[A-Z][a-z][0-9][.][_]%',col)>0)