--建立如下函数(非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('[^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 area like('%[^0-9]%')
set @s='23*******'
select left(@s,patindex('%[^0-9]%',@s)-1)
/*
----------
23(1 個資料列受到影響)*/
set @s='11654asdfs刘德华'
select left(@s,patindex('%[^0-9]%',@s)-1)/*
------------------------------
11654(所影响的行数为 1 行)*/
--建立如下函数(非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('[^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 area like('%[^0-9]%')