--
提取数字
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
转载于:https://www.cnblogs.com/justwannaloveyou/archive/2010/10/23/1858862.html