SQL中获取字符串中的字母,数字,非数字函数

 

--sql获取字母:

CREATE FUNCTION dbo.F_Get_STR (@S VARCHAR(100))

RETURNS VARCHAR(100)

AS

BEGIN

    WHILE PATINDEX('%[^a-z]%',@S)>0

          BEGIN

                set @s=stuff(@s,patindex('%[^a-z]%',@s),1,'')

          END

    RETURN @S

END

 

--sql获取数字:

create function dbo.F_Get_Number (@S varchar(100))

returns int

AS

begin

    while PATINDEX('%[^0-9]%',@S)>0

          begin

                set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')

          end

    return cast(@S as int)

end

 

--sql获取非数字:

create function dbo.F_Get_NANnumber (@S varchar(100))

returns varchar(500)

AS

begin

    while PATINDEX('%[0-9]%',@S)>0

          begin

                set @s=stuff(@s,patindex('%[0-9]%',@s),1,'')

          end

    return @s

end