用函数模拟正则表达式

原贴地址

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

















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















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