几个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
-- ------------------------------------------------------------------
-- 过滤重复字符
IF OBJECT_ID ( ' DBO.DISTINCT_STR ' ) IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR
GO
CREATE FUNCTION DBO.DISTINCT_STR( @S NVARCHAR ( 100 ), @SPLIT VARCHAR ( 50 ))
RETURNS VARCHAR ( 100 )
AS
BEGIN
IF @S IS NULL RETURN ( NULL )
DECLARE @NEW VARCHAR ( 50 ), @INDEX INT , @TEMP VARCHAR ( 50 )
IF LEFT ( @S , 1 ) <> @SPLIT
SET @S = @SPLIT + @S
IF RIGHT ( @S , 1 ) <> @SPLIT
SET @S = @S + @SPLIT
WHILE CHARINDEX ( @SPLIT , @S ) > 0 AND LEN ( @S ) <> 1
BEGIN
SET @INDEX = CHARINDEX ( @SPLIT , @S )
SET @TEMP = LEFT ( @S , CHARINDEX ( @SPLIT , @S , @INDEX + LEN ( @SPLIT )))
IF @NEW IS NULL
SET @NEW = ISNULL ( @NEW , '' ) + @TEMP
ELSE
SET @NEW = ISNULL ( @NEW , '' ) + REPLACE ( @TEMP , @SPLIT , '' ) + @SPLIT
WHILE CHARINDEX ( @TEMP , @S ) > 0
BEGIN
SET @S = STUFF ( @S , CHARINDEX ( @TEMP , @S ) + LEN ( @SPLIT ), CHARINDEX ( @SPLIT , @S , CHARINDEX ( @TEMP , @S ) + LEN ( @SPLIT )) - CHARINDEX ( @TEMP , @S ), '' )
END
END
RETURN RIGHT ( LEFT ( @NEW , LEN ( @NEW ) - 1 ), LEN ( LEFT ( @NEW , LEN ( @NEW ) - 1 )) - 1 )
END
GO
PRINT DBO.DISTINCT_STR( ' A,A,B,C,C,B,C, ' , ' , ' )
-- A,B,C
GO
-- ------------------------------------------------------------------
-- 过滤重复字符2
IF OBJECT_ID ( ' DBO.DISTINCT_STR2 ' ) IS NOT NULL
DROP FUNCTION DBO.DISTINCT_STR2
GO
CREATE FUNCTION DBO.DISTINCT_STR2( @S varchar ( 8000 ))
RETURNS VARCHAR ( 100 )
AS
BEGIN
IF @S IS NULL RETURN ( NULL )
DECLARE @NEW VARCHAR ( 50 ), @INDEX INT , @TEMP VARCHAR ( 50 )
WHILE LEN ( @S ) > 0
BEGIN
SET @NEW = ISNULL ( @NEW , '' ) +LEFT ( @S , 1 )
SET @S = REPLACE ( @S , LEFT ( @S , 1 ), '' )
END
RETURN @NEW
END
GO
SELECT DBO.DISTINCT_STR2( ' AABCCD ' )
-- ABCD
GO

-- ------------------------------------------------------------------
IF OBJECT_ID ( ' DBO.SPLIT_STR ' ) IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar ( 8000 ),      -- 包含多个数据项的字符串
@INDEX int ,             -- 要获取的数据项的位置
@SPLIT varchar ( 10 )     -- 数据分隔符
)
RETURNS VARCHAR ( 100 )
AS
BEGIN
IF @S IS NULL RETURN ( NULL )
DECLARE @SPLITLEN int
SELECT @SPLITLEN = LEN ( @SPLIT + ' A ' ) - 2
WHILE @INDEX > 1 AND CHARINDEX ( @SPLIT , @S + @SPLIT ) > 0
SELECT @INDEX = @INDEX - 1 , @S = STUFF ( @S , 1 , CHARINDEX ( @SPLIT , @S + @SPLIT ) + @SPLITLEN , '' )
RETURN ( ISNULL ( LEFT ( @S , CHARINDEX ( @SPLIT , @S + @SPLIT ) - 1 ), '' ))
END
GO
PRINT DBO.SPLIT_STR( ' AA|BB|CC ' , 2 , ' | ' )
--
GO