在SQLserver中stuff 函数用法

--提取数字
IF?OBJECT_ID('DBO.GET_NUMBER')?IS?NOT?NULL
DROP?FUNCTION?DBO.GET_NUMBER
GO
CREATE?FUNCTION?DBO.GET_NUMBER(@S?VARCHAR(100))
RETURNS?100)
AS
BEGIN
WHILE?PATINDEX(%[^0-9]%',@S)?>?0
set?@s=stuff(@s,patindex(@s),1,0)">'')
END
RETURN?@S
测试PRINT?DBO.GET_NUMBER(呵呵ABC-12,3ABC')
123
--------------------------------------------------------------------提取英文DBO.GET_STRFUNCTION?DBO.GET_STR
FUNCTION?DBO.GET_STR(%[^a-z]%PRINT?DBO.GET_STR(呵呵ABC123ABC提取中文DBO.CHINA_STRFUNCTION?DBO.CHINA_STR
FUNCTION?DBO.CHINA_STR(NVARCHAR(%[^吖-座]%SET?@S?=?STUFF(@S,0)">@S),NPRINT?DBO.CHINA_STR(过滤重复字符DBO.DISTINCT_STRFUNCTION?DBO.DISTINCT_STR
FUNCTION?DBO.DISTINCT_STR(100),0)">@SPLIT?50))
IF?IS?NULL?RETURN(NULL)
DECLARE?@NEW?50),0)">@INDEX?INT,0)">@TEMP?50)
IF?LEFT(1)<>@SPLIT
=?@SPLIT+RIGHT(@SCHARINDEX(@SPLIT,0)">@S)>0?AND?LEN(<>1
@INDEX?@S)
@TEMP?=?@INDEX+@SPLIT)))
NULL?
@NEW?ISNULL(@NEW,0)">'')@TEMP
ELSE
REPLACE(@TEMP,0)">@SPLIT),0)">@SPLIT))-RETURN?RIGHT(@NEW)-1),255)">LEN(1))1)
PRINT?DBO.DISTINCT_STR(A,A,B,C,,128)">A,C过滤重复字符2DBO.DISTINCT_STR2FUNCTION?DBO.DISTINCT_STR2
FUNCTION?DBO.DISTINCT_STR2(varchar(8000))
@NEW+LEFT(@NEW
SELECT?DBO.DISTINCT_STR2(AABCCDABCDGO

DBO.SPLIT_STRFUNCTION?DBO.SPLIT_STR
FUNCTION?DBO.SPLIT_STR(
8000),??????包含多个数据项的字符串int,?????????????要获取的数据项的位置10)?????数据分隔符
)
@SPLITLEN?int
SELECT?@SPLITLENA')2
WHILE?1?@SPLIT)=@SPLITLEN,255)">RETURN(ISNULL(''))
PRINT?DBO.SPLIT_STR(AA|BB|CC2,0)">|--
GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!