create function getstr (@string varchar(1000)) returns varchar(1000) as begin
DECLARE @position int,@string2 varchar(1000) set @position=1 set @string2='' WHILE @position<len(@string)+1 begin if(ASCII(SUBSTRING(@string, @position, 1))<58) begin set @string2=@string2+SUBSTRING(@string, @position, 1) end set @position=@position+1 end return @string2 endselect dbo.getstr('1,2-7,8AA,BB9')/* --------- 1,2-7,8,9
用一個函數處理IF OBJECT_ID('f_str') IS NOT NULL DROP FUNCTION f_str go CREATE FUNCTION f_str( @s nvarchar(1000) ) RETURNS nvarchar(1000) begin WHILE PATINDEX('%[^0-9,-]%',@s)>0 SET @s=STUFF(@s,PATINDEX('%[^0-9,-]%',@s),1,'') RETURN @s END go DECLARE @str NVARCHAR(1000) SET @str='1,2-7,8AA,BB9' SELECT dbo.f_str(@str) /*1,2-7,8,9 */
create function getstr (@string varchar(1000))
returns varchar(1000)
as
begin
DECLARE @position int,@string2 varchar(1000)
set @position=1
set @string2=''
WHILE @position<len(@string)+1
begin
if(ASCII(SUBSTRING(@string, @position, 1))<58)
begin
set @string2=@string2+SUBSTRING(@string, @position, 1)
end
set @position=@position+1
end
return @string2
endselect dbo.getstr('1,2-7,8AA,BB9')/*
---------
1,2-7,8,9
DROP FUNCTION f_str
go
CREATE FUNCTION f_str(
@s nvarchar(1000)
)
RETURNS nvarchar(1000)
begin
WHILE PATINDEX('%[^0-9,-]%',@s)>0
SET @s=STUFF(@s,PATINDEX('%[^0-9,-]%',@s),1,'')
RETURN @s
END
go
DECLARE @str NVARCHAR(1000)
SET @str='1,2-7,8AA,BB9'
SELECT dbo.f_str(@str)
/*1,2-7,8,9
*/