sql 提取数字、字母、汉字

 

代码

-- 提取数字
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
-- ------------------------------------------------------------------
-- 提取英文
IF   OBJECT_ID ( ' DBO.GET_STR ' )  IS   NOT   NULL
DROP   FUNCTION  DBO.GET_STR
GO
CREATE   FUNCTION  DBO.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
GO
-- 测试
PRINT  DBO.GET_STR( ' 呵呵ABC123ABC ' )
GO
-- ------------------------------------------------------------------
-- 提取中文
IF   OBJECT_ID ( ' DBO.CHINA_STR ' )  IS   NOT   NULL
DROP   FUNCTION  DBO.CHINA_STR
GO
CREATE   FUNCTION  DBO.CHINA_STR( @S   NVARCHAR ( 100 ))
RETURNS   VARCHAR ( 100 )
AS
BEGIN
WHILE   PATINDEX ( ' %[^吖-座]% ' , @S )  >   0
SET   @S   =   STUFF ( @S , PATINDEX ( ' %[^吖-咗]% ' , @S ), 1 ,N '' )
RETURN   @S
END
GO
PRINT  DBO.CHINA_STR( ' 呵呵ABC123ABC ' )
GO