用函数模拟正则表达式

原贴地址

http://topic.csdn.net/u/20100127/09/b92af1cb-f7ab-4015-b36a-921e03d043a0.html?86487

 

------------------------------------------------------烤鸭专用分割线 ------------------------------------------------------------------------

问题描述

 

最近在做一个出售号码的网站,客户有这样一个搜索需求,按号码规则(AABB、ABCDABCD、AB*AB* ……)搜索号码。
如:
1、用户按规则“AABB”搜索,则将以下结果显示出来:
1301122 3456
130124455 68
13012348899
……

2、用户按规则“ABCDABCD”搜索,则将以下结果显示出来:
1381234 1234
1387531 7531
……

2、用户按规则“AB*AB*”搜索,则将以下结果显示出来:
18912 512 345
1890075 475 9
……

现使用的方法,是在程序里拼凑规则查询语句,但效率十分低。如规则为“AABB”则生成Sql语句为:
…… AND ([No] LIKE '%0011%' OR [No] LIKE '%0022%' …… OR [No] LIKE '%9988%') 差不多100个OR语句

请教各位高手有什么更好的解决方案,望赐教,谢谢大家

------------------------------------------------------烤鸭专用分割线------------------------------------------------------------------------

解决方案



IF

OBJECT_ID
(
'
TB
'
)
IS

NOT

NULL

DROP

TABLE
TB


IF

OBJECT_ID
(
'
FUN_MU
'
)
IS

NOT

NULL

DROP

FUNCTION
FUN_MU


GO



CREATE

TABLE
TB(COL1
VARCHAR
(
50
))


INSERT

INTO
TB


SELECT

'
13011223456
'

UNION

ALL



SELECT

'
13012445568
'

UNION

ALL



SELECT

'
13012348899
'

UNION

ALL



SELECT

'
13812341234
'

UNION

ALL



SELECT

'
13875317531
'

UNION

ALL



SELECT

'
13012212456
'

UNION

ALL



SELECT

'
18912512345
'

UNION

ALL



SELECT

'
18900754759
'



GO



CREATE

FUNCTION
FUN_MU(
@STR

VARCHAR
(
50
),
@COL

VARCHAR
(
50
))


RETURNS

TINYINT



AS



BEGIN



DECLARE

@STR1

VARCHAR
(
50
),
@STR2

VARCHAR
(
50
),
@I

INT
,
@LEN

INT
,
@RETURN

INT



DECLARE

@STABLE

TABLE
(ID
INT

IDENTITY
(
1
,
1
),COL1
INT
)


DECLARE

@CTABLE

TABLE
(ID
INT

IDENTITY
(
1
,
1
),COL1
INT
)


SELECT

@STR1
=
@COL
,
@STR2
=
@STR
,
@I
=
1
,
@LEN
=
LEN
(
@STR
)


WHILE

@I
<=
LEN
(
@STR1
)


BEGIN



INSERT

INTO

@STABLE
(COL1)


SELECT

ASCII
(
SUBSTRING
(
@STR1
,
@I
,
1
))


SET

@I
=
@I
+
1



END



SET

@I
=
1



WHILE

@I
<=
LEN
(
@STR2
)


BEGIN



INSERT

INTO

@CTABLE
(COL1)


SELECT

ASCII
(
SUBSTRING
(
@STR2
,
@I
,
1
))


SET

@I
=
@I
+
1



END



IF

EXISTS
(


SELECT
S.ID
-
C.ID,
COUNT
(
DISTINCT
S.COL1
-
C.COL1)


FROM

@STABLE
S


CROSS

JOIN

@CTABLE
C


WHERE
S.ID
-
C.ID
IN
(


SELECT
S.ID
-
C.ID


FROM

@STABLE
S


CROSS

JOIN

@CTABLE
C


WHERE
S.ID
-
C.ID
>=
0



GROUP

BY
S.ID
-
C.ID
HAVING

COUNT
(
1
)
=
@LEN
)


AND
C.COL1
<>
42



GROUP

BY
S.ID
-
C.ID
HAVING

COUNT
(
DISTINCT
S.COL1
-
C.COL1)
=
1


)


SET

@RETURN
=
1



ELSE



SET

@RETURN
=
0





RETURN

@RETURN



END



GO



DECLARE

@STR

VARCHAR
(
50
)


SET

@STR
=
'
AB*AB*
'



SELECT
COL1,DBO.FUN_MU(
@STR
,COL1)
FROM
TB







------------------------------------------------------烤鸭专用分割线------------------------------------------------------------------------







相当于用函数模拟了正则表达式的部分功能,如果需要的话还可以返回匹配的具体位置










相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页