用函数模拟正则表达式

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

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

1、用户按规则“AABB”搜索，则将以下结果显示出来：
1301122 3456
130124455 68
13012348899
……

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

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

…… 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

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



07-12 2983
06-14 342
06-21 2万+
02-20 506
05-18 3257
12-01 163
01-25 6973
06-13 4513
01-19 2197
03-25 1万+
08-06 962
07-11
03-03 159
09-28