34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb(code varchar(20))
insert into tb values('521501!^%&95')
insert into tb values('5201!^%&95')
go
select replace(code,'!^%&','') from tb
drop table tb
/*--------------------------
52150195
520195
(2 行受影响)
*/
--提取数字
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
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
--看到别人这么写的!很有用
declare @aa char(80),@bb char(80)
set @aa = 'dfsa1231dad!!!'
set @bb = substring(@aa,PATINDEX( '%[0-9]% ',@aa), len(@aa)-PATINDEX( '%[0-9]% ',@aa)+1)
if patindex( '%[a-z]% ' ,@bb) <> 0
select convert(int,substring(@bb,1,patindex('%[a-z]% ',@bb)-1)) shuzi
else
select @bb
shuzi
-----------
1231
(1 行受影响)
create table tb(code varchar(20))
insert into tb values('521501!^%&95')
insert into tb values('5201!^%&95')
go
create function f_numeric(@str varchar(100))
returns varchar(100)
as
begin
declare @i int
declare @len int
declare @str1 varchar(100)
set @str1=''
set @len=len(@str)
set @i=1
while @i<=@len
begin
if isnumeric(substring(@str,@i,1))>0
begin
set @str1=@str1+substring(@str,@i,1)
end
else
begin
set @str1=@str1+','
end
set @i=@i+1
end
return replace(@str1,',','')
end
go
select dbo.f_numeric(code) as code from tb
/**
code
-------------------------
52150195
520195
(2 行受影响)
**/
drop function f_numeric
drop table tb