declare @s varchar(10) set @s = '88+spp/67yi' while patindex('%[a-z+/]%',@s) > 0 set @s = stuff(@s,patindex('%[a-z-+/]%',@s),1,'')select @s---------- 8867(1 行受影响)
declare @s varchar(100) declare @i int declare @len int declare @str1 varchar(100) set @s='88+spp/67yi' --你的字符串 set @str1='' set @len=len(@s) set @i=1 while @i<=@len begin if isnumeric(substring(@s,@i,1))>0 begin set @str1=@str1+substring(@s,@i,1) end else begin set @str1=@str1+',' end set @i=@i+1 end select replace(replace(@str1,',',''),'+','')
[a-z+/] 指范围 a-z 和 + / 中的任何单个字符. 用[]来指定一个范围
指定范围 如: ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。
--创建函数 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') END RETURN @S END GO --测试 SELECT DBO.GET_NUMBER2('88+spp/67yi') /*---------------------------------------------------------------------------------------------------- 8867(1 行受影响)*/
create table tb(col varchar(20)) insert into tb values('88+spp/67yi') insert into tb values('88+spp/68yi') 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('[^0-9]') set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '') else set @i = @i +1 return @oldstr end goselect col = dbo.getnewstr(col) from tb where col like('%[^0-9]%')drop table tb drop function dbo.getnewstr/* col --------- 8867 8868(所影响的行数为 2 行) */
DECLARE @S VARCHAR(20) SET @S='88+spp/67yi' WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'') END SELECT @S /* -------------------- 8867(1 個資料列受到影響)*/
eclare @s varchar(10) set @s = '88+spp/67yi' while patindex('%[a-z+/]%',@s) > 0 set @s = stuff(@s,patindex('%[a-z-+/]%',@s),1,'')select @s
set @s = '88+spp/67yi' while patindex('%[a-z+/]%',@s) > 0
set @s = stuff(@s,patindex('%[a-z-+/]%',@s),1,'')select @s----------
8867(1 行受影响)
declare @i int
declare @len int
declare @str1 varchar(100)
set @s='88+spp/67yi' --你的字符串
set @str1=''
set @len=len(@s)
set @i=1
while @i<=@len
begin
if isnumeric(substring(@s,@i,1))>0
begin
set @str1=@str1+substring(@s,@i,1)
end
else
begin
set @str1=@str1+','
end
set @i=@i+1
end
select replace(replace(@str1,',',''),'+','')
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
GO
--测试
SELECT DBO.GET_NUMBER2('88+spp/67yi')
/*----------------------------------------------------------------------------------------------------
8867(1 行受影响)*/
insert into tb values('88+spp/67yi')
insert into tb values('88+spp/68yi')
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('[^0-9]')
set @oldstr = replace(@oldstr, substring(@oldstr, @i, 1), '')
else
set @i = @i +1
return @oldstr
end
goselect col = dbo.getnewstr(col) from tb where col like('%[^0-9]%')drop table tb
drop function dbo.getnewstr/*
col
---------
8867
8868(所影响的行数为 2 行)
*/
SET @S='88+spp/67yi'
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
SELECT @S
/*
--------------------
8867(1 個資料列受到影響)*/
set @s = '88+spp/67yi' while patindex('%[a-z+/]%',@s) > 0
set @s = stuff(@s,patindex('%[a-z-+/]%',@s),1,'')select @s