CREATE FUNCTION f_str(@Value NVARCHAR(1000)) RETURNS NVARCHAR(1000) AS BEGIN WHILE PATINDEX('%[^0-9]%',@Value)>0 SELECT @Value=STUFF(@Value,PATINDEX('%[^0-9]%',@Value),1,'') RETURN @Value END
以上函數功能將把非數字的內容去掉 --用法 select dbo.f_str(列) as 列 from Table
create function FN_GetNumber ( @str varchar(8000) ) returns varchar(8000) begin declare @str2 varchar(8000) declare @i int select @i=len(@str),@str2='' while @i>=0 begin if unicode(left(@str,1)) between 48 and 57 select @str2=@str2+left(@str,1) select @str=stuff(@str,1,1,'') select @i=@i-1 end return @str2 end godeclare @str varchar(8000) select @str='23lkjdf 2134;lkjdfg了中' select dbo.fn_getnumber(@str)drop function dbo.fn_getnumber
create function get_shuzi(@instr varchar(1000)) returns varchar(1000) as begin declare @str varchar(800) declare @object int declare @r varchar(800) set @str='var data = "'+@inStr+'";var reCat = /[^1234567890,*]/gi;data.replace(reCat,"");' exec sp_OACreate 'MSScriptControl.ScriptControl',@object output exec sp_OASetProperty @object, 'Language','javascript' exec sp_OAMethod @object, 'eval', @r out,@str return @r end go--正则的!
create function get_shuzi(@instr varchar(1000)) returns varchar(1000) as begin declare @str varchar(800) declare @object int declare @r varchar(800) set @str='var data = "'+@inStr+'";var reCat = /[^1234567890]/gi;data.replace(reCat,"");' exec sp_OACreate 'MSScriptControl.ScriptControl',@object output exec sp_OASetProperty @object, 'Language','javascript' exec sp_OAMethod @object, 'eval', @r out,@str return @r end go--正则的!上边多了两个符号的筛选,这个对的。
最进迷上了这xml 可能和楼主要的不一样~~不为成分只为分享!!!create table #tb ( id int identity(1,1), vars varchar(1000) ) insert into #tb select '090 3 833 590 (aTuaán)' union all select '090 3 854v33 590 (aTuaán)' union all select '04f90 3 854v33 590 (aTuaán)' union all select '090 3 8533 59d0 (aTuaán)' select stuff((SELECT (case when PATINDEX('%[^0-9]%',id)=0 then ' '+id else null end) FROM (SELECT [value] = CONVERT(XML , '<r>' + REPLACE( t.vars, ' ' , '</r><r>')+ '</r>') ) A OUTER APPLY ( SELECT id = N.r.value('.' , 'varchar(100)') FROM A.[value].nodes('/r') N (r) ) B for xml path('')),1,1,'') from #tb t -------------------- 090 3 833 590 090 3 590 3 590 090 3 8533(4 行受影响)
CREATE FUNCTION f_str(@Value NVARCHAR(1000)) RETURNS NVARCHAR(1000) AS BEGIN WHILE PATINDEX('%[^0-9]%',@Value)>0 SELECT @Value=STUFF(@Value,PATINDEX('%[^0-9]%',@Value),1,'') RETURN @Value END 这个就不错了!
RETURNS NVARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@Value)>0
SELECT @Value=STUFF(@Value,PATINDEX('%[^0-9]%',@Value),1,'')
RETURN @Value
END
--用法
select dbo.f_str(列) as 列 from Table
create function FN_GetNumber
(
@str varchar(8000)
)
returns varchar(8000)
begin
declare @str2 varchar(8000)
declare @i int
select @i=len(@str),@str2=''
while @i>=0
begin
if unicode(left(@str,1)) between 48 and 57
select @str2=@str2+left(@str,1)
select @str=stuff(@str,1,1,'')
select @i=@i-1
end
return @str2
end
godeclare @str varchar(8000)
select @str='23lkjdf 2134;lkjdfg了中'
select dbo.fn_getnumber(@str)drop function dbo.fn_getnumber
create function get_shuzi(@instr varchar(1000))
returns varchar(1000)
as
begin
declare @str varchar(800)
declare @object int
declare @r varchar(800)
set @str='var data = "'+@inStr+'";var reCat = /[^1234567890,*]/gi;data.replace(reCat,"");'
exec sp_OACreate 'MSScriptControl.ScriptControl',@object output
exec sp_OASetProperty @object, 'Language','javascript'
exec sp_OAMethod @object, 'eval', @r out,@str
return @r
end
go--正则的!
create function get_shuzi(@instr varchar(1000))
returns varchar(1000)
as
begin
declare @str varchar(800)
declare @object int
declare @r varchar(800)
set @str='var data = "'+@inStr+'";var reCat = /[^1234567890]/gi;data.replace(reCat,"");'
exec sp_OACreate 'MSScriptControl.ScriptControl',@object output
exec sp_OASetProperty @object, 'Language','javascript'
exec sp_OAMethod @object, 'eval', @r out,@str
return @r
end
go--正则的!上边多了两个符号的筛选,这个对的。
(
id int identity(1,1),
vars varchar(1000)
)
insert into #tb select '090 3 833 590 (aTuaán)'
union all select '090 3 854v33 590 (aTuaán)'
union all select '04f90 3 854v33 590 (aTuaán)'
union all select '090 3 8533 59d0 (aTuaán)'
select
stuff((SELECT (case when PATINDEX('%[^0-9]%',id)=0 then ' '+id else null end)
FROM (SELECT [value] = CONVERT(XML , '<r>' + REPLACE( t.vars, ' ' , '</r><r>')+ '</r>')
) A
OUTER APPLY ( SELECT id = N.r.value('.' , 'varchar(100)')
FROM A.[value].nodes('/r') N (r)
) B
for xml path('')),1,1,'')
from #tb t
--------------------
090 3 833 590
090 3 590
3 590
090 3 8533(4 行受影响)
CREATE FUNCTION f_str(@Value NVARCHAR(1000))
RETURNS NVARCHAR(1000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@Value)>0
SELECT @Value=STUFF(@Value,PATINDEX('%[^0-9]%',@Value),1,'')
RETURN @Value
END
这个就不错了!