;198056058?
;198061201?
;198062333我有一个表里,会员卡号在录入时,出现如上符号,我现在想把它update 掉,变成如下要求:
198056058
198061201
198062333
;198061201?
;198062333我有一个表里,会员卡号在录入时,出现如上符号,我现在想把它update 掉,变成如下要求:
198056058
198061201
198062333
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 area = dbo.getnewstr(area) from tb where area like('%[^0-9]%')
insert into tb values(';198056058?')
insert into tb values(';198061201?')
insert into tb values(';198062333')
gogo
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 id = dbo.getnewstr(id) from tb where id like('%[^0-9]%')drop function getnewstr
drop table tb/*
id
----------------------------------------------------------------------------------------------------
198056058
198061201
198062333(所影响的行数为 3 行)
*/
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
--------------------------------------------------------------------
消息 208,级别 16,状态 1,第 2 行
对象名 'DBO.GET_NUMBER' 无效。